+ Reply to Thread
Results 1 to 3 of 3

Setting a Variable range in VBA

  1. #1
    The Hawk
    Guest

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

  2. #2
    Norman Jones
    Guest

    Re: 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" <[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...




  3. #3
    The Hawk
    Guest

    Re: 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" <[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...

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1