Fixed number of columns - Variable number of rows in Excel

[sysmanint1]

I am a total neophyte at Visual Basic but found the following post and reply from Clint concerning a dynamic range.

Also, have never "posted" to a discussion

I have made a macro that works on a template spreadsheet with a fixed number of columns to be "macro'd" bounded by data that changes in its number of rows.

That is, the bounding data is imported to the spreadsheet before the calculated columns are run. But the rows of this data may be 20 rows or 2000 rows. My macros will always stop at the number of rows on which I recorded the data. That is, my example was 322 rows so now the 2000 row spreadsheet will only have 322 rows of 5 columns of calculated data. Obviously, I simply copy down and I get my desired result. But does your response dynamically adjust for differing number of rows?

when you responded to the person in the example below you wrote:

ya GL2:GL" & NbRows

Can you format that as a replacement to one of the questioner's lines please?

I have tried replacing the last Range line and I get an error

Range("GL2:GL2064").Select

changed to

Range("GL2:GL"& NbRows).Select

Simply, I want to be able to "copydown" 5 columns of calculating data based on adjoining rows that will be variable in number from spreadsheet to spreadsheet.


Thank you

Carol Locke


-------------------------------------------------------------------------------------------------------

Dim varNbRows As Double

'Determine how many records are in the file
varNbRows = Selection.CurrentRegion.Rows.Count

Range("GL2").Select
ActiveCell.FormulaR1C1 = "=(RC[-106]/RC[-105])"
Range("GL2").Select
Selection.AutoFill Destination:=Range("GL2:GL2064"), Type:=xlFillDefault
Range("GL2:GL2064").Select

I need to change the 2064 with varNbRows-any suggestions?

--------------------------------------------------------------------------------

Reply
iam_clint
Senior Member
168 Posts July 26th, 2006
07:22 PM
#2


Re: new to VB for Excel-cell range of a macro
--------------------------------------------------------------------------------

ya GL2:GL" & NbRows
__________________
- I reject your reality and substitute my own -