Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a user-set autofilter value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a user-set autofilter value
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a user-set autofilter value
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reading a user-set autofilter value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
How to count rows in a user-defined AutoFilter or AdvancedFilter is active? | Excel Programming | |||
Code to allow user to enter criteria for autofilter | Excel Programming | |||
Excel reading user defined fields in Outlook | Excel Programming |