Get last row from filtered range

Question

How do you find the last row of data when the data on your worksheet is filtered? I play with Special Cells and Visible Cells but cannot find a solution. I think it should be some kind of variation on what I have below:

  ... With ws LR = .Range("A" & Rows.Count).End(xlUp).Row .Range("A1:E" & LR).AutoFilter Field:=2, Criteria1:="=4" LRfilt = .Range("A" & Rows.SpecialCells(xlCellTypeVisible).Count).End(xlUp).Row Debug.Print LR Debug.Print LRfilt End With ... 

The file can be found here:

wikisend.com/download/443370/FindLRFilteredData.xls

Edit:

Having realized after a discussion with Siddhart, I did not want the Last Row property to be needed to find the counter of the number of visible lines that led to the Sid solution below ...

Answer 1

EDIT: After-sales service after

 Option Explicit Sub FilterTest() Dim rRange As Range, fltrdRng As Range, aCell As Range, rngToCopy As Range Dim ws As Worksheet Dim LR As Long '~~> Change this to the relevant sheet For Each ws In ThisWorkbook.Worksheets If Not ws.Name = "Sheet1" Then With ws '~~> Remove any filters .AutoFilterMode = False LR = .Range("A" & Rows.Count).End(xlUp).Row '~~> Change this to the relevant range Set rRange = .Range("A1:E" & LR) With rRange '~~> Some Filter. Change as applicable .AutoFilter Field:=2, Criteria1:=">10" '~~> Get the filtered range Set fltrdRng = .SpecialCells(xlCellTypeVisible) End With For Each aCell In fltrdRng If aCell.Column = 1 Then If rngToCopy Is Nothing Then Set rngToCopy = aCell Else Set rngToCopy = Union(rngToCopy, aCell) End If End If Next Debug.Print ws.Name Debug.Print rngToCopy.Address 'rngToCopy.Copy Set rngToCopy = Nothing '~~> Remove any filters .AutoFilterMode = False End With End If Next End Sub 

Answer 2

After the filter, using the same formula for the last one, the last filtered string will be returned:

 ... With ws LR = .Range("A" & Rows.Count).End(xlUp).Row .Range("A1:E" & LR).AutoFilter Field:=2, Criteria1:="=4" LRfilt = .Range("A" & Rows.Count).End(xlUp).Row Debug.Print LR Debug.Print LRfilt End With ... 

Answer 3

It seems to work. When the filters are on the normal .end (xlUp), returns the last row of the filtered range, but not the last row of the sheet. I suggest you use this technique to get the last line:

 Sub GetLastRow ' Find last row regardless of filter If Not (ActiveSheet.AutoFilterMode) Then ' see if filtering is on if already on don't turn it on Rows(1).Select ' Select top row to filter on Selection.AutoFilter ' Turn on filtering End if b = Split(ActiveSheet.AutoFilter.Range.Address, "$") ' Split the Address range into an array based on "$" as a delimiter. The address would yeild something like $A$1:$H$100 LastRow= Val(b(4)) ' The last value of the array will be "100" so find the value End sub 

Answer 4

This is the simplest solution.

 ... With ws .Range("A1:E1").AutoFilter Field:=2, Criteria1:="=4" LRfilt=.Range("A1", .Range("A1").End(xlDown)).End(xlDown).Row Debug.Print LRfilt End With ...