Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA command to select to end of row/col? (Excel 2000/SR2)

    Is there a VBA command or sequence of commands to replicate the manual selection of the rest of a row or column by double-clicking on the relevant active cell edge?

    I am writing a macro to apply formats and actions to a range of cells and it would be nice if I did not have to manually select my (varying) range before running the macro.

    Thanks in anti ... ci .. pation!

    Perc

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    Hi,
    I think you want the Range.End(direction) property.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    <pre> ActiveCell.End(xlToRight).Select
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    Thanks for the prompt replies. They got me going in the right direction, and the end result is attached.

    If anybody has time to critique it I'd appreciate any feedback, as I am sure there must be a few other ways of achieving the same end..
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    Hello Peter,

    Your macro is a nice excercise in analyzing the worksheet. However, it can be simplified.

    In the first place, there is no need to compute the row and column of the active cell by using InstrRev etc. Use the Row and Column properties of the Range object instead.

    But a Range object also has a tremendously useful property CurrentRegion - it denotes the rectangular area around it bordered by empty rows and columns.

    The following macro will select the area from the active cell to the bottom right corner of this region:

    Sub TestSelect()
    Dim CurRegion As Range
    Dim EndCell As Range
    Set CurRegion = ActiveCell.CurrentRegion
    Set EndCell = CurRegion(CurRegion.Rows.Count, CurRegion.Columns.Count)
    Range(ActiveCell, EndCell).Select
    End Sub

    The Excel wizards on the Lounge will no doubt be able to come up with a one-liner that does the same.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    How about:
    <pre>Sub SelectToEnd()
    Range(ActiveCell, ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLa stCell)).Select
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    Great! That's what I meant - there is (almost) always somebody on the Lounge who knows just the right trick!

  8. #8
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    Uh huh - you are SO right! That's why I like it - it can save me considerable angst and time.

    As you can see from the thread, it also helps being able to articulate one's question capably in the first place! The final result suggested being so much more elegant and concise than my sledgehammer offering that I blush. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks again.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    This also works great:

    ActiveCell.CurrentRegion.Select

    Mario

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA command to select to end of row/col? (Excel 2000/SR2)

    I and the others who replied to the original post assumed that he wanted to select the block from the active cell to the bottom right corner of the block it is in. That is select to the right and downwards.

    ActiveCell.CurrentRegion selects the whole block the active cell is in, including cells to the left and above the active cell.

Posting Permissions

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