How to use a variable in refering a range?

nicholasho

New Member
Joined
Oct 10, 2005
Messages
6
Hi,

I like to define a range to cover a column in a sheet. One of the method is as follows:


Set LookupSheet = Worksheets("Sheet5")
LookupLastRow = LookupSheet.Range("A65536").End(xlUp).Row


Set RangeOne = Worksheets("Sheet5").Range("$AX$2:$AX$(LookupLastRow)")

The last statement has syntax error with LookupLastRow. What's the syntax to retrieve the variable value and used in the range definition?

Thanks and Regards
Nick
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Nick

Code:
Set RangeOne = Worksheets("Sheet5").Range("$AX$2:$AX$" & LookupLastRow)

Best regards

Richard
 

Watch MrExcel Video

Forum statistics

Threads
1,118,096
Messages
5,570,198
Members
412,308
Latest member
jt145
Top