Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Range With xlDown Doesn't Work (97 SR2)

    I
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Range With xlDown Doesn't Work (97 SR2)

    If column V is guaranteed not to have empty cells, then you can use:

    <pre> Range("F3", Range("V1").Offset(Range("V3").End(xlDown).Row - 1, 0)).Select
    </pre>


    If there is any chance of an empty cell in V, then the following will always work:

    <pre> Range("F3", Range("V1").Offset(Range("V65536").End(xlUp).Row - 1, 0)).Select
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Range With xlDown Doesn't Work (97 SR2)

    Legare,
    The first code works great but I am trying to understand WHY it works.

    I read about the Offset Method and it said it needed an input range, a row offset, and a column offset. So I
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Range With xlDown Doesn't Work (97 SR2)

    F3 is the upper left corner of the area you want to select, and has nothing to do with the Offset property. Range("F3", specifies the upper left cell of the range that you want to select.

    The Range("V1") is the "input range" for the Offet property. In other words, that is cell from which Offset will be used to specify the bottom right cell of the range to be selected.

    Range("V3").End(xlDown).Row will find the row number of the row above the first empty cell in column V after row 3 (in case row 1 or 2 is empty). This minus one is used as the row offset, and the column offset is zero since we are offsetting from column V already. The minus 1 is because offset zero is required to specify the starting cell. So, if V4 is empty, the resulting row number would be 3. Since we are offsetting from V1, a row offset of 0 would be V1, an offset of 1 would be V2, and an offset of 2 would be V3 (which is what we want in this case). Therefore, to get the correct offset we must subtract one from the row number.
    Legare Coleman

Posting Permissions

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