Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding last filled cell (2000 sr1)

    I need to find the last filled cell in a column (and sometimes in a row). There are many gaps in the data.
    For the column I tried this...

    Sub SelectLastCellInColumn()
    Dim LastCell

    ActiveCell.Columns("A:A").EntireColumn.Select
    ActiveCell.Offset(Selection.EntireColumn.Rows.Coun t - 1).Activate
    SendKeys ("^{up}")
    ActiveCell.Range("A1").Select
    'LastCell = ActiveCell.Address
    'MsgBox (LastCell)
    End Sub
    The remmed out lines at the end are because up to that point it seems to work, but I cannot figure out why the next two lines actually tell me the active cell is in row 65536!

    Tell me an easy way to do what I am obviously trying to do the long way around.
    Neil Eustice
    Woody Worshipper

  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: finding last filled cell (2000 sr1)

    Hi,
    I think this will do what you want for a column:
    ActiveCell.Offset(ActiveSheet.UsedRange.Rows.Count , 0).End(xlUp).Select
    You can modify it as necessary for a row.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: finding last filled cell (2000 sr1)

    Neil, here's how I do it:<pre>Option Explicit
    Sub Ends()
    Dim lastInColumn As Range
    Dim lastInRow As Range
    With ActiveSheet.UsedRange
    Set lastInColumn = Cells(.Row + .Rows.Count, ActiveCell.Column).End(xlUp)
    Set lastInRow = Cells(ActiveCell.Row, .Column + .Columns.Count).End(xlToLeft)
    End With
    MsgBox lastInColumn.Address & " is last in the current column."
    MsgBox lastInRow.Address & " is last in the current row."
    End Sub</pre>

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: finding last filled cell (2000 sr1)

    Sam and Rory, I think I have raised this question before, and I want to double check with you. In my experience the danger with UsedRange is that if you want the last used cell of a specific column, and there is another column in the sheet with a last used cell "further down" (higher row number) than the target column, you get the longest columns' row reported, which is not the last cell in the target column intended. Which is why I've used structures like (and this is not an exact answer to the original question, and is untested in a specific application):

    Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Address

    I welcome clarification!
    -John ... I float in liquid gardens
    UTC -7ħDS

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding last filled cell (2000 sr1)

    It does exactly what I want. Thanks a million.
    Neil Eustice
    Woody Worshipper

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding last filled cell (2000 sr1)

    Works great. Thanks a lot.
    Neil Eustice
    Woody Worshipper

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding last filled cell (2000 sr1)

    Call me thick, but I am not sure how to use this line to get what I want!
    Neil Eustice
    Woody Worshipper

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

    Re: finding last filled cell (2000 sr1)

    John: If you look at Rory's code, you will see that he uses UsedRange just to get a row number higher than any used row, then he uses .End(xlUp) to search back up the designated column for the last used cell. So, even though the UsedRange might give a row higher than the one wanted, the .End(xlUp) should bring it back to where it should be.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: finding last filled cell (2000 sr1)

    Thick? You're not with Andersen! Returns the address of the last cell in the cell or column selected.

    Sub LastRow()
    MsgBox Cells(Application.Rows.Count, Selection.Column).End(xlUp).Address
    End Sub
    -John ... I float in liquid gardens
    UTC -7ħDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: finding last filled cell (2000 sr1)

    You (and Rory and Sam) are correct, I didn't follow how the offset bit in Rory's code and the ActiveCell.Column bit in Sam's code works. Thanks!
    -John ... I float in liquid gardens
    UTC -7ħDS

  11. #11
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding last filled cell (2000 sr1)

    Cheers mate. Works a treat.
    Neil Eustice
    Woody Worshipper

  12. #12
    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: finding last filled cell (2000 sr1)

    Additionally, the problem with UsedRange is that it isn't always what you or I would consider to be the usedrange - it sometimes includes cells that used to have stuff in them even if they don't anymore. Hence my going past the usedrange and then using the End(xlUp) bit. I've always found it odd though that Ctrl+End seems to take these phantom cells into account whilst Ctrl+Down (or whichever direction) doesn't seem to.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: finding last filled cell (2000 sr1)

    Rory, one problem with your formula, it assumes that there are no leading blank rows. That's why I added in UsedRange.Row: it may not be one. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    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: finding last filled cell (2000 sr1)

    Thanks, Sam.
    I confess that didn't even occur to me! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    New Lounger
    Join Date
    Jan 2001
    Location
    Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding last filled cell (2000 sr1)

    <P ID="edit" class=small>Edited by gwhitfield on 24-Jan-02 18:49.</P>Hyperlinks added

    See the method at <A target="_blank" HREF=http://www.beyondtechnology.com/geeks012.shtml>http://www.beyondtechnology.com/geeks012.shtml</A>

    To find the last row used use use Lastcell(activesheet).rowt
    To find the last column used use Lastcell(activesheet).column

    Ron

Page 1 of 2 12 LastLast

Posting Permissions

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