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

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



"Vinoth"

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

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

Vinoth wrote:
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