MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count Number of Filtered Records


Posted by JAF on March 15, 2001 7:24 AM

Hi

I have a small piece of code (part of a much larger macro) that runs an AutoFilter on a column of data.

What I need to do is after running the AutoFilter to generate a variable that is the value of the number of filtered entries - in Engish: If I have 17 filtered items that match the specified criteria, I need to generate a variable to the value of 17.

The code I'm using to run the AutoFilter is:
Selection.AutoFilter Field:=1, Criteria1:="New"

Any suggestions as to how I can count the number of filtered entries on my list?

NB: I know that I could use COUNTIF to get this number, but I really need to use the AutoFilter if at all possible.

JAF


Posted by Dave Hawley on March 15, 2001 7:43 AM

Hi JAF

Sounds like you want the subtotal function:

Sub TryThis()
Dim i As Integer
i = WorksheetFunction.Subtotal(3, Columns(1))
MsgBox i
End Sub


1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP


Dave

OzGrid Business Applications