Tom Ogilvy's function worked fine for me using Data|Autofilter. In fact, the
code is written to work with autofilter. Look at the code and you'll see a few
"sh.autofilter"

I'm gonna guess that you're not pointing to the correct cell in your worksheet
formula.

Could that be the case?

(I like to point at the cell that contains the dropdown arrow.)



Rob wrote:

Dave,

I tried this function but the macro of David Ritchie uses the expression:

If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If

Filtermode seems to target to the enhanced filter, not to autofilter. For
even when autofilter is active AND it has a selection the sh.filtermode is
False...

Any other suggestions?

Rob

"Dave Peterson" schreef in bericht
...
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

--

Dave Peterson