Debra Dalgleish posted a response from Tom Ogilvy to get the filter criteria:

http://groups.google.co.uk/group/mic...00cb43ac5960ae

or
http://tinyurl.com/ajndr

Then after you have the criteria, you could use =subtotal(3,...) to count the
visible cells in that filtered range:

msgbox application.subtotal(3,activesheet.autofilter.rang e.columns(1)) -1

Choose a column that has a header and has data in each cell.


Rob wrote:

Dear fellownewsgroupreaders,

in a list of fruits and their amounts a user uses autofilter to filter out
'apples' and 'green ones'. The result (with a Subtotal formula) is given on
te sheet (6)

Now I want to print a result in a separate sheet, saying:

Fruit Type Amount
Apples Green ones 6

What I need is a little VBA code that picks out the choosen value 'apples'
out of the filter setting

I started with:

Sub ReadFilter()

dim F
Dim V1
Dim V2
set F = worksheets("fruits").autofilter

v = F. <how do I get the first value?

end sub

Now in I can see the active filter AND an Item 1 (note the space between
Item and 1), a criteria1 with "Apples" in it. How do I get this value in a
variable? How do I know how much filters the user set?

Regards,
Rob

--

Dave Peterson