Any way to get the real last row not affected by autofilter? Without altering current autofilter status, using UsedRange nor looping cells from bottom

Question

Surprisingly it seems that the most common ways to get the last row are all affected by autofilter.

No matter it's .Find, .End(xlUp), .SpecialCells...once the range is filtered, I just can't get the "real" last row.

Is there any way to get the last row of the worksheet as if it's unfiltered? Without turning off or resetting the autofilter? Thank you very much!

Answer 1

I thought this question has been asked over and over again and there are trillions of answers on the internet, on SO and elsewhere, but to be honest I couldn't find one that fulfilled all requirements.

Some thoughts:
If you organize your data in tables, the task is rather easy, see the answer of Frank Ball. Also, when you use tables, things like Find or even Cells(Rows.Count, col).End(xlUp).row work when data is filtered.

Okay, let's assume that your data is not in a table.

Using UsedRange nowadays is much more reliable than it was when questions/answers like this Find last used cell in Excel VBA where discussed. In earlier days, you had to store/reload a workbook to get UsedRange updated when some data at the end was removed. This is no longer the case, it is updated immediately. Main drawback to use UsedRange is that if you have cells that are formatted (eg with a color), those cells are included even if they contain no data. What's most important is that UsedRange always contain all used cells.

If your data doesn't contain any empty rows, easiest way often is to use CurrentRegion. Take any cell that contains data (most often the header row), and use CurrentRegion on that.

Function FindLastRowUsingCurrentRegion(startCell As Range)
    With startCell.CurrentRegion
        FindLastRowUsingCurrentRegion = .Cells(.Cells.Count).row
    End With
End Function

However, if your data can contain empty rows, this is not an option. In that case, the idea provided at Find the last filled row in a filtered column without dropping the Autofilter is your best bet. I mentioned in the comments that I don't like the code itself, so here is my attempt.

The code reads all the data of a specific column that is inside UsedRange into memory to speed up the process. Now there is only a small issue: UsedRange doesn't start at row 1 of the sheet if the first row(s) are empty. When looping over the data read into memory, the index need to match the row number, so with a little trick I force to start always at row 1.

Function FindLastRowUsingUsedRange(Optional ws As Worksheet = Nothing, Optional col As Long = 1) As Long
    If ws Is Nothing Then Set ws = ActiveSheet
    Dim usedRange As Range, r As Range
    Set usedRange = ws.Range(ws.Cells(1, 1), ws.usedRange.Cells(ws.usedRange.Cells.Count))
    
    Set r = Intersect(usedRange, ws.Cells(1, col).EntireColumn)
    ' Debug.Print r.Address
    If r.Count = 1 Then     ' Empty sheet
        FindLastRowUsingUsedRange = 1
        Exit Function
    End If

    Dim data, row As Long
    data = r.Value
    For row = UBound(data) To 1 Step -1
        If Not IsEmpty(data(row, 1)) Then
            FindLastRowUsingUsedRange = row
            Exit Function
        End If
    Next row
End Function

This works no matter if your data is in a table or not, if it contains empty rows or not and if the data is filtered or not. And it is fast: I intentionally set the color of cell A1048576, which led in my test sheet to a UsedRange of $A$4:$K$1048576. Running the function gave the result almost instantly (and my computer is far from being high end).

Answer 2

Is there any other data on the sheet? If not, then this will give you the number of rows in the Table whether it's filtered or not and you simply add to it the number rows used in your header and/or extra rows above the Table:

Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Rows.Count