How do you find the last row of data when the data in your worksheet is filtered? I have been playing around with Special Cells
and Visible Cells
but cannot find a solution. I think it must 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
...
File can be found here:
wikisend.com/download/443370/FindLRFilteredData.xls
Edit:
Realised after discussion with Siddharth I did not want the Last Row
property I needed to find a count of the number of visible rows which led on to Sid's solution below...
After the filter, using the same formula for the lastrow will return the last filtered row:
...
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
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With