Count of results in AutoFilter

[andrew]

Hello all,

I have an autofilter running through a macro - but I would
like to retrieve the number of rows that met the filter
criteria.

Is there a .count property (or something similar) on the
AutoFilter object? If not, is there a countif(visible)
formula I could use directly in the worksheet?

Thanks!!!!

 

[Tom Ogilvy]

set rng = Activesheet.autofilter.Range.Columns(1)
set rng1 = rng.SpecialCells(xlVisible)
msgbox rng1.Count - 1 ' subtract the header

 

[Guest]

Perfect.

Thanks!

-----Original Message-----
set rng = Activesheet.autofilter.Range.Columns(1)
set rng1 = rng.SpecialCells(xlVisible)
msgbox rng1.Count - 1 ' subtract the header

--
Regards,
Tom Ogilvy




.

 

[Steve Smallman]

in the worksheet, use the subtotal function with function 2 for numeric and
3 for alpha data.

In a list of alpha data, add beneath the list the function
=SUBTOTAL(3,range)

Steve