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