Jacob's code returns the number of rows in the UsedRange. This will equate to
the last row of the AutoFilter range if the AutoFilter range starts from row
1. If the AutoFilter is set up with a number of rows at the top of the screen
before the column headers then Jacob's code does not return the worksheet row
number of the last row of AutoFilter. (I often set up AutoFilter with a
number of rows at the top of the screen and I Freeze Panes from under the
column headers so that area of the screen is always visible and I place
various subtotal functions in the frozen area.)
Anyway the following returns the actual worksheet row number of the last row
of AutoFilter. (Not the last visible row; last row of AutoFiltered Range.)
With Sheets("Sheet1").AutoFilter.Range
lastRow = .Rows(.Rows.Count).Row
End With
The following can be used for last visible row but it is unreliable if data
has been added below the Autofilter range and then deleted. (Saving the file
seems to fix this but see the next example that I have found more reliable.)
With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .SpecialCells(xlLastCell).Row
End With
Following method seems more reliable for last visible row. It only looks
within the Autofiltered range and including SearchDirection:=xlPrevious and
After:=.Cells(1, 1), forces it to start looking at the last cell in the last
row of the range. Note that SearchOrder:=xlByRows is also important.
With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .Cells _
.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
End With
