VBA TIPS
Count filtered rows count

When you would like to COUNT filtered rows count, it seems the following code works.

Activesheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count

But this doesn't work as intended when the AutoFilter.Range has some Areas. In the case that the filtered data is as follows, the above code returns 1. Because in this case, the filtered range is separated from 5 areas.
Try this code. Excel returns $C$8,$C$10,$C$12,$C$14,$C$23.

Activesheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Address



So, to get all filtered rows count, you need to sum of each Area's rows count using a loop. (See Function FilteredRowsCount)
But there is another way you can do the same thing without using a loop. (See Function FilteredRowsCount2)

Those UDFs return the same result.

Example

Place the following in a standard module.

Sub Test()
    MsgBox FilteredRowsCount(ActiveSheet) & "  data was found."
    MsgBox FilteredRowsCount2(ActiveSheet) & " data was found."
End Sub

Function FilteredRowsCount(ByVal Sh As Worksheet)
    Dim Target As Range
    Dim c As Range
    Dim i As Long
    'If the Filter is not used
    If Sh.FilterMode = False Then
        FilteredRowsCount = 0
        Exit Function
    End If
    Set Target = Sh.AutoFilter.Range
    For Each c In Target.SpecialCells(xlCellTypeVisible).Areas
        i = i + c.Rows.Count
    Next
    FilteredRowsCount = i - 1    '-1 stands for remove header row
End Function

Function FilteredRowsCount2(ByVal Sh As Worksheet)
    Dim Target As Range
    'If the Filter is not used
    If Sh.FilterMode = False Then
        FilteredRowsCount2 = 0
        Exit Function
    End If
    Set Target = Sh.AutoFilter.Range
    FilteredRowsCount2 = _
    Target.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
End Function

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo