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
