Setting a Variable range in VBA
 

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

Setting a Variable range in VBA
 

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


---
Regards,
Norman


"The Hawk" wrote in message
...
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...

Setting a Variable range in VBA
 

Thanks Norman. You were right about the definition.

"Norman Jones" wrote:

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


---
Regards,
Norman


"The Hawk" wrote in message
...
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...