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?

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

"Steve" wrote in message
...
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?