CODEWITHSUNDEEP
excelvba
Ish15june
Ish15june

Reputation: 11

VBA : How to count number of rows in filtered column?

I have an Excel sheet with four columns of data.

I have applied auto filter to second column. Now I want to count the number of rows after applying filter.

Here is the code used to apply auto filter:

Sub filtered_row_count()

Sheets("Sheet1").Select
row_count = Application.CountA(Range("B:B")) - 1 'Subtract the header

Selection.AutoFilter Field:=2, Criteria1:="cat"

End Sub

How to find the number of rows after this filter has been applied?

Upvotes: 1

Views: 16091

Answers (1)

SY Chen
SY Chen

Reputation: 149

How about count the visible cells after applying filter.

Sub filtered_row_count()

Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:="cat"
K = Range("B:B").SpecialCells(xlCellTypeVisible).Count
CNT2 = Range("B65535").End(xlDown).Row
CNT3 = Range("B" & CNT2).End(xlUp).Row

Data_Count = (CNT3 + K - 1) - CNT2
MsgBox Data_Count

End Sub

Upvotes: 1

Related Questions