How to get the count of filtered rows after applying autofilter?

[Vinoth]

Hi,

I've written a macro to autofilter on a particular column of a worksheet.
Now i need to get the count of filtered rows (no. of visible records). I'm
not sure how to use the 'Count' property of autofilter. I've written a code
block to do this work for me which consumes a lot of time.

ActiveSheet.Range("$A$1:$AX$5000").AutoFilter Field:=FilterColumn2,
Criteria1:=FilterValue2, Operator:=xlFilterValues

TotalNumber = 0

For j = 2 To 5000
If Cells(j, 1) <> "" Then
If Cells(j, 1).EntireRow.Hidden = False Then
TotalNumber = TotalNumber + 1
End If
Else
Exit For
End If
Next

Is there a way to get the count by using count property rather than using
the above code block?

Many Thanks,
Vinoth

 

[Jim Cone]

One approach from Debra Dalgleish here...
http://www.contextures.on.ca/xlautofilter02.html#Count
--
Jim Cone
Portland, Oregon USA



"Vinoth"
<[email protected]>
wrote in message
Hi,
I've written a macro to autofilter on a particular column of a worksheet.
Now i need to get the count of filtered rows (no. of visible records). I'm
not sure how to use the 'Count' property of autofilter. I've written a code
block to do this work for me which consumes a lot of time.

ActiveSheet.Range("$A$1:$AX$5000").AutoFilter Field:=FilterColumn2,
Criteria1:=FilterValue2, Operator:=xlFilterValues

TotalNumber = 0

For j = 2 To 5000
If Cells(j, 1) <> "" Then
If Cells(j, 1).EntireRow.Hidden = False Then
TotalNumber = TotalNumber + 1
End If
Else
Exit For
End If
Next

Is there a way to get the count by using count property rather than using
the above code block?

Many Thanks,
Vinoth

 

[keiji kounoike]

Looking at your code, I guess you want the count of rows from the top
continuously not hidden and not blank. for example like below

row1 -->> shown and not blank
rpw2 -->> shown and not blank
row3 -->> shown and not blank
row4 -->> hidden
row5 -->> shown
row6 -->> hidden
row7 -->> shown

The answer you want to get is 2.
and the data like below

row1 -->> shown and blank
rpw2 -->> hidden and blank
row3 -->> shown and not blank
row4 -->> hidden
row5 -->> shown
row6 -->> hidden
row7 -->> shown

The answer you want to get is 0.

Is this what you want?

Keiji