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!
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).