Setting a Variable range in VBA

[Guest]

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
'<<=============

 

[Guest]

Thanks Norman. You were right about the definition.

 

[Supercaboose]

...
'=============>>
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
'<<=============

...

Hi,
I'm not trying to resurrect a dead thread but, (contradictory statement here) how would you define "B" as a variable? I've tried to replace "B" with MyClmn and defined it as a Range. That didn't work. Any ideas? This is what I'm working with

======>>
Sub Testing()
Dim rng As Range
Dim LastRow As Long
Dim MyClmn As Range

Set MyClmn = Range("H")

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

Set rng = Range("H51:H" & LastRow)
rng.Select

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

I did this because I want it to be able to change for different parts of a much larger script for auto formatting different columns of variable lengths. Also, I am self taught with this stuff so please be gentle.

Thanks!
~Luke~

 

[Supercaboose]

Ok...

Dim MyClmn As Range 'Changed to Dim MyClmn As String
Set MyClmn = Range("H") 'Changed to MyClmn = "H"