CODEWITHSUNDEEP
excelvba
Dat Nguyen
Dat Nguyen

Reputation: 163

Excel VBA - Count number of rows for Filtered Table . Keep getting 4 instead of 0

I'm trying to count the row after I filter the table, but keep getting 4 instead of 0. So I'm not sure where I got the code wrong? 

Sub Test_filter()

' Test_filter Macro

    Rows("4:4").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$F$53").AutoFilter Field:=6, Criteria1:="14,982"
    Range("M54") = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

End Sub

And when I filter the result, even though I see 0 rows, but it keeps giving me 4 for the counted rows. SO i'm not sure why?

Upvotes: 1

Views: 335

Answers (2)

Dat Nguyen
Dat Nguyen

Reputation: 163

Thank you QHarr. It works great. If I try to loop this code for other files within the folder, is there a way for me to generalize the filtered range so that it can be applied to different workbooks with different sizes of table?

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You are using the used range to do the count. Change to the filtered range e.g.

Range("M54") = ActiveSheet.Range("$A$4:$F$53").SpecialCells(xlCellTypeVisible).Rows.Count

Subtract 1 to remove header.

Upvotes: 1

Related Questions