Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last cell of hidden range (2003)

    Hi all.
    I am trying to get the row number of the last row in a table of data. However the last few rows are hidden and it is the row number of the hidden row that I want.
    I would normally do
    cells(1,1).end(xldown).row
    however this will stop at the last visible cell
    if I do
    cells(1,1).end(xldown).end(xldown).row
    it takes me to the very last cell in the spreadsheet
    I can't take advantage of the usedrange as there is other data lower down on the spreadsheet that needs to remain.

    Any help would be much appreciated

    Amanda

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Last cell of hidden range (2003)

    Is the last number of rows that are hidden a constant. For eg. Is it always 3 rows hidden at the bottom? If this is the case you can use your approach and add the ammount of hidden rows to that...

    Something like:

    Sub GetRowNum()
    Dim myRow As Integer
    myRow = Range("A65536").End(xlUp).Row
    myRow = myRow + 3 'If three is the amount of constant hidden rows
    End Sub
    Regards,
    Rudi

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

    Re: Last cell of hidden range (2003)

    If there are no used cells below the hidden cells in column A, you can use

    Range("A:A").Find(What:="*", SearchDirection:=xlPrevious).Row

    to find the row number of the last non-blank cell. The Find method doesn't take cells being visible or hidden into account, it just looks at the contents.

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

    Re: Last cell of hidden range (2003)

    Does this do what you want? (I've added a msgbox to display the last hidden row number)

    Sub hidRows()
    Dim visRow As Long, nextVisRow As Long, i As Long, lastHiddenRow as Long

    visRow = Cells(1, 1).End(xlDown).Row
    nextVisRow = Cells(visRow, 1).End(xlDown).Row

    For i = visRow + 1 To nextVisRow
    If Cells(i, 1).EntireRow.Hidden <> True Then
    Exit For
    End If
    Next

    lastHiddenRow = i - 1
    MsgBox "last hidden row =" & lastHiddenRow

    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last cell of hidden range (2003)

    Thanks to all replies

    The one Hans gave worked a treat.
    In the meantime I also worked out this solution, but it is not as neat as Hans'.

    Cells.Find("Area").End(xlDown).Offset(1, 0).Select
    Range(ActiveCell.Address, Cells(Rows.count, 1)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Thanks Amanda

Posting Permissions

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