# Thread: Last cell of hidden range (2003)

1. ## 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. ## 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

3. ## 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. ## 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. ## 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