Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
1.6k views
Welcome To Ask or Share your Answers For Others

1 Answer

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...