CODEWITHSUNDEEP
excelvba
Kasia
Kasia

Reputation: 31

Count filtered rows

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.

Upvotes: 3

Views: 10062

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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

Upvotes: 4

Tom
Tom

Reputation: 9878

This is due to that you're referring to the entire column instead of just your table. Try with

rows_count = Range(cells(1,"AX"), cells(cells(rows.count,"AX").end(xlup).row,"AX")).SpecialCells(xlCellTypeVisible).Count

It would be better to declare which sheet you're referrring to so use

With Sheets("Sheet1")
    rows_count = Range(.Cells(1, "AX"), .Cells(.Cells(.Rows.Count, "AX").End(xlUp).Row, "AX")).SpecialCells(xlCellTypeVisible).Count
End With

Where Sheet1 is the name of your sheet you're referring to.

Or if you're using a table object you can get your answer with

With Sheets("Sheet1").ListObjects(1)
    rows_count = .ListColumns(Columns("AX").Column).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
End With

Upvotes: 0

Related Questions