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

Question

I'm trying to find the last row in column A that contains a value with the following code:

LastRow = DataWorksheet.Range("A:A").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

This works fine for most cases, except when the last few rows are filtered out. For instance, let's say we have 30 rows of data. If rows 1-10 are visible, 11-20 are filtered out, and 21-30 are visible, it finds the last row successfully: it returns 30. When everything is visible and rows 21-30 are filtered out, LastRow returns 1.

Note that if I manually hide instead of filtering out rows 21-30, it tells me that the last row is 20.

What gives? How can I make it determine what the last row is if the last rows are filtered?

Edit: Now it seems as though LastRow is picking out the last unfiltered row, which is a definite departure from its previous behavior. I'll update this post once I'm better able to isolate the bug/inconsistency I'm encountering.

Answer 1

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?