Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CurrentRegion Last Cell (XP)

    How does one determine what the address is of the last cell withing a region?

    I can select a current region by using Range("A1").CurrentRegion.Select. In this example let's say the region is A150 with D50 being the address of the last cell in the region.

    Thanks,
    John

  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: CurrentRegion Last Cell (XP)

    <pre>With Range("A1").CurrentRegion
    msgbox .Cells(.Cells.Count).Address
    End With
    </pre>

    should work
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CurrentRegion Last Cell (XP)

    <P ID="edit" class=small>(Edited by sdckapr on 25-Apr-08 10:25. Corrected error)</P>To put it into a message:

    Msgbox "Last Cell in Region is "& Range("A1").CurrentRegion.specialcells(xlCellTypeL astCell).Address

    To add it to a variable:

    Dim sAdd as String
    sAdd = Range("A1").CurrentRegion.specialcells(xlCellTypeL astCell).Address

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CurrentRegion Last Cell (XP)

    Something along the lines of:

    Cells(Range("A1").CurrentRegion.End(xldown).row,Ra nge("A1").CurrentRegion.End(xltoright).column)

    Last Row in the current region:
    Range("A1").CurrentRegion.End(xldown).row

    Last column in the current region:
    Range("A1").CurrentRegion.End(xltoright).column

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CurrentRegion Last Cell (XP)

    Steve/Rory,

    Thank you for your suggestions. I came up with another:

    Dim Rng As range
    Set Rng = range("A1").CurrentRegion
    MsgBox Rng(Rng.Count).Address

    Regards,
    John

Posting Permissions

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