Setting a Variable range in VBA

[The Hawk]

I'm trying to define a variable range based on the row before the last one
that contains data. Have tried a variety of methods, but so far none that
work. Due to methods used to load drop down controls the last row contains
<End>; therefore, I need to use the row right above as the control for sorts,
etc. The Column Range is fixed so I've tried;

Range("B3:AE" & VARIABLE).Select

where the VARIABLE is the last row offset -1. The results always include
<End> in the sort.

Any advice will be appreciated...

 

[Norman Jones]

Hi Hawk,

Perhaps your problem relates to the definition of the last row,

In any case the following works for me:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim LastRow As Long

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

Set rng = Range("B3:AE" & LastRow - 1)
rng.Select

End Sub
'<<=============

 

[The Hawk]

Thanks Norman. You were right about the definition.