Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Question about Autofilter...
Hello All,
I am currently filtering a list using the Autofilter function within Excel. Great! However, I would like to produce a Summary Report in a seperate worksheet based on the results of the filter. Does anyone know a method of how to obtain criteria on which the Autofilter is currently filtering on. Eg. If I filter a column based on say the word "EDGE", how can you automatically, obtain this from the autofilter.... Many thanks in advance, Richard |
#2
|
|||
|
|||
Record a macro while you are doing this, select the range and press F5,
special and then visible cells only, copy and and paste -- Regards, Peo Sjoblom "Richard Latter" wrote in message om... Hello All, I am currently filtering a list using the Autofilter function within Excel. Great! However, I would like to produce a Summary Report in a seperate worksheet based on the results of the filter. Does anyone know a method of how to obtain criteria on which the Autofilter is currently filtering on. Eg. If I filter a column based on say the word "EDGE", how can you automatically, obtain this from the autofilter.... Many thanks in advance, Richard |
#3
|
|||
|
|||
Eg. If I filter a column based on say the word "EDGE", how can you
automatically, obtain this from the autofilter.... Perhaps you might want to try this UDF from a previous post by Tom Ogilvy in microsoft.public.excel.programming Subject: Read AutoFilter Criteria Here is a user defined function that will display the criteria in a cell: -- begin vba -- Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- end vba -- To implement the UDF: Press Alt+F11 to go to VBE Click Insert Module Copy and paste the UDF* into the white space on the right *everything within the dotted lines Alt+Q to get back to Excel In Excel, if you have an autofilter effected in col C (say) you could use Tom's UDF by putting in say D1: =showfilter(C:C) D1 will return the filter criteria effected in col C -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Richard Latter" wrote in message om... Hello All, I am currently filtering a list using the Autofilter function within Excel. Great! However, I would like to produce a Summary Report in a seperate worksheet based on the results of the filter. Does anyone know a method of how to obtain criteria on which the Autofilter is currently filtering on. Eg. If I filter a column based on say the word "EDGE", how can you automatically, obtain this from the autofilter.... Many thanks in advance, Richard |
#4
|
|||
|
|||
Hi Richard
If you filter on one column you can use this Add-in to do this. Very easy to use http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Richard Latter" wrote in message om... Hello All, I am currently filtering a list using the Autofilter function within Excel. Great! However, I would like to produce a Summary Report in a seperate worksheet based on the results of the filter. Does anyone know a method of how to obtain criteria on which the Autofilter is currently filtering on. Eg. If I filter a column based on say the word "EDGE", how can you automatically, obtain this from the autofilter.... Many thanks in advance, Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frozen panes in Excel should stay put when using autofilter. | Excel Discussion (Misc queries) | |||
New Project, Different Problem: AutoFilter? | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Can I AutoFilter an entire workbook? | Excel Discussion (Misc queries) | |||
Strange Results with Autofilter | Excel Discussion (Misc queries) |