How To Count Rows Displayed After Applying AutoFilter

[Steve]

I'm trying to write some VBA code to determine the number of rows being
displayed after applying a filter. This would be similar to what's
displayed in the lower left-hand corner of the Excel Window after applying a
filter. (Using the example below, it shows "4 of 10 records found")

Assume that I have 10 rows in a worksheet. After using AutoFilter to show
only rows without "ABC" in Col A and with "Closed" in Col B, I am looking
for a count of, say, 4. However, the code below always gives me the total
count of 10. That is, it ignores the applied filter.

Sub Macro()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criticial:="<ABC", Operator:=xlAnd
Selection.AutoFilter Field:=2, Criterial:="Closed"
MsgBox Selection.CurrentRegion.Rows.Count
End Sub

Anyone know what code will give me the count of 4 I am looking for?

 

[JMay]

The Subtotal() function works on Unhidden rows Only.
For Example, Subtotal(9,B1:B100) in B101 will sum only the Values in only
the Visible Rows
Subtotal(3,B1:B100) in B102 will count only the Visible Rows.
This of course is the function method of accomplishing, not the VBA although
you can incorporate the function in your code (I can't help with that)
perhaps someone else can,,,
HTH

 

[Ed]

In the second cell of a blank column, I have this formula:
=SUBTOTAL(3,$A$2:A2)
which is run down the range of the column. After applying the autofilter, I
can select this column and force it to calculate, and it counts just the
visible rows. Of course, you have to go to the bottom of the colummn to see
the number. That's not a problem for me. For pure automation, you would
probably need a macro that you would run after every autofilter to force a
calculation on that column, run to the bottom and get the value, than
display it.

HTH
Ed