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

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" <[email protected]> wrote in message
news:[email protected]...
> 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...

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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...
>
>
>