Is there a function to retreive the parameters of a filter?

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

I am trying to label a chart that is based on the summary
of data resulting from various filtering. I would like to
put a title on the chart that corresponds to the filter
that was used (autofilter). Is there a function or a
vbstatement that I could use to capture the column that is
being filtered and the parameters of the filter,( ie. )
returning first the column heading: ie. Overruns and then
the filter parameter: ie. Over 20%. Once captured in a
cell, I would like to put these on the chart.

Please let me know if anyone else has done this, and how I
would approach this,
Thank you.
Amy
 
One way:

This will recover the header and first criterion for the leftmost column
in which a filter is set.

Dim i As Long
Dim sHeader As String
Dim sCriterion As String
With Sheets("Sheet4").AutoFilter.Filters
For i = 1 To .Count
With .Item(i)
If .On Then
sHeader = .Parent.Range(1, i).Value
sCriterion = .Criteria1
Exit For
End If
End With
Next i
End With


Not sure what you intend by "put these on a chart" - in a textbox? a
legend? a title?

Note that a criterion of "Over 20%" will be returned as ">0.2"
 
Amy,

Here is a reply from Tom Ogilvy, Excel MVP:
Start Quote
Click to expand...

Here is a user defined function that reports the criteria being applied in a
single autofilter dropdown. You should be able to adapt to your requirement
(- loop through all columns and find those with a criteria -).
As written, you would put in
=ShowFilter(C1)

with C1 being any cell in the column you want to check.



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

Regards,
Tom Ogilvy
End Quote
Click to expand...

HTH,
Bernie
MS Excel MVP
 
Back
Top