CODEWITHSUNDEEP
excelvba
Yang
Yang

Reputation: 172

Filtered Row Count

In VBA, I wish to find the row count of a filtered column, so I wrote VBA code as 

FilteredRowCount = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

But FilteredRowCount always return value of 1, what would cause this?

Upvotes: 1

Views: 154

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Do like this

Sub test()
    Dim Ws As Worksheet
    Dim rngDB As Range
    Dim r As Integer
    Dim rng As Range

    Set Ws = ActiveSheet
    Set rngDB = Ws.UsedRange.SpecialCells(xlCellTypeVisible)

    For Each rng In rngDB.Areas
        r = r + rng.Rows.Count
    Next rng
    MsgBox r

End Sub

Upvotes: 2

Related Questions