Solved: Find last row and set range name programmatically

[RKramkowski]

Hi,

I have a spreadsheet that gets updated periodically and the number of rows may change each time. I'm doing a number of counts and calculations but these counts and calculations have to be done on an entire column. I can go in and set a range name manually but how can I set the range name to a range each time the spreadsheet is opened and a macro run?

The sequence of events would be as follows:

1. User opens spreadsheet
2. User clicks macro button
3. Macro finds the last row and sets the range names for several columns from row2 (to allow for the header row) to the end of the column. All columns will be the same length and do not have blanks.
4. Macro now proceeds to do calculations using the ranges that have been set.

I've done some searches but haven't been able to piece this together.

Thanks,
Bob

[Bob Phillips]

With Activesheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("A2").Resize(LastRow - 1).Name = "ColARange"
.Range("B2").Resize(LastRow - 1).Name = "ColBRange"
'etc.
End With

[RKramkowski]

Thanks! This worked perfectly!