Thanks Thanks:  0
Results 1 to 6 of 6

Thread: Selecting Range using .end(xldown) & .end(xlright)

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selecting Range using .end(xldown) & .end(xlright)

    Hi there! This is definately a quick question, but I need to select a range. I'm looking to do so along these lines:
    Code:
    Range("Activecell.End(xlDown)", "Active.End(xlToRight)").Select
    This is definately a problem of not knowing the right jargon to do so.

    Could someone please assist?

    Thanks in advance!
    final

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Selecting Range using .end(xldown) & .end(xlright)

    Try

    Code:
    Dim LR As Long, LC As Long
    LR = ActiveCell.End(xlDown).Row
    LC = ActiveCell.End(xlToRight).Column
    Range(ActiveCell, Cells(LR, LC)).Select
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting Range using .end(xldown) & .end(xlright)

    Quote Originally Posted by VoG View Post
    Try

    Code:
    Dim LR As Long, LC As Long
    LR = ActiveCell.End(xlDown).Row
    LC = ActiveCell.End(xlToRight).Column
    Range(ActiveCell, Cells(LR, LC)).Select
    Elegant solution, ty. But for curiosity, is it possible to do as I originally inteded? maybe using "Range(cells(),cells()).select"?

    thanks you in advance once again.

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Selecting Range using .end(xldown) & .end(xlright)

    As a one-liner

    Code:
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting Range using .end(xldown) & .end(xlright)

    Quote Originally Posted by VoG View Post
    As a one-liner

    Code:
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select

    Beautifull, I was trying to do so, but I was forgetting the .row & .column and couldn't see what i was doing wrong.
    Thanks!

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Selecting Range using .end(xldown) & .end(xlright)

    You are welcome.

    For what it's worth you don't actually need to select that range in order to work with it. For example

    Code:
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Interior.ColorIndex = 3
    HTH, Peter
    Please test any code on a copy of your workbook.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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