Count filtered rows

Question

Excel - VBA I want to count how many rows there are after filtering the table. How do I do this? I have tried rows_count = Range("AX:AX").SpecialCells(xlCellTypeVisible).Count but that gives me full number of rows there are in Excel 2010.

Answer 1

Once you've applied your filter, just use something like this:

rows_count = Worksheets("Sheet1").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

---

For example, I filled A2:C20 with =RAND() and used the following code:

Sub filter()
    Dim sht As Worksheet
    Dim rng As Range

    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set rng = sht.Range("A1:C20")

    sht.AutoFilterMode = False
    rng.AutoFilter Field:=1, Criteria1:="<0.5"

    MsgBox sht.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1        
End Sub