Finding the last row of an Excel spreadsheet when the last row is hidden

These should ignore filtering / visibility and give you the last used row number:

DataWorksheet.UsedRange.Rows.Count

-or-

DataWorksheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

Neither will find the last used cell in column A, however... is that what you need?

How about this (as a sort of work around on XL's limitations). It's kind of long / clunky with the loop, but at least the loop starts at the first visible last cell.

LastRow = DataWorksheet.Range("A:A").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).RowIf LastRow <> DataWorksheet.UsedRange.Rows.Count 'assumes data starts in A1, if not adjust acoordingly'now check if there is anything below    Dim rngSearch as Range    rngSearch = DataWorksheet.Range("A" & LastRow & ":A" & DataWorksheet.UsedRange.Rows.Count)    Dim lngRows as Long, lngCnt as Long    lngRows = rngSearch.Rows.Count    For lngCnt = lngRows to 1 Step -1        If DataWorksheet.Range("A" & lngCnt) = vbNullString And DataWorksheet.Range("A" & lngCnt -1) <> vbNullString Then            LastRow = DataWorksheet.Range("A" & lngCnt-1).Row        End If    NextEnd If

This works on sheets with both hidden rows and autofilters. It will also NOT give you the incorrect row if a cell below the last cell with a value has been formatted (which will cause the usedrange to be greater than the row you are looking for).

Sub FindLastRowWithValue()    Dim ws As Worksheet    Dim temp As Worksheet    Dim lastrow As Long    ' copy the sheet that may have hidden rows    Set ws = Sheets("Sheet1")    ws.Copy Before:=Sheets(1)    Set temp = ActiveSheet    ' turn off autofiltering if need be    If temp.AutoFilterMode Then temp.AutoFilterMode = False    ' unhide all rows    temp.Columns("A:A").EntireRow.Hidden = False    ' get the last row with a value now that all rows are unhidden    lastrow = temp.Range("A" & temp.Rows.Count).End(xlUp).Row    ' delete the temporary sheet    Application.DisplayAlerts = False    temp.Delete    Application.DisplayAlerts = True    MsgBox lastrowEnd Sub