Showing criteria in custom filter
 

I would like to show in a cell the criteria entered into a custom filter of
data for example, if the custom criteria was set as = 3000 then either
enter 3000 in a cell or the text "greater than or equal to 3000"

Is this possible within a worksheet function? any examples would be most
welcome.

Thanks, Rob

Showing criteria in custom filter
 

Courtesy of Tom Ogilvy

You use it like so

=showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32)

The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed

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




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
I would like to show in a cell the criteria entered into a custom filter
of
data for example, if the custom criteria was set as = 3000 then either
enter 3000 in a cell or the text "greater than or equal to 3000"

Is this possible within a worksheet function? any examples would be most
welcome.

Thanks, Rob

Showing criteria in custom filter
 

This is just brilliant, thanks Bob and of course Tom for creating the piece
of work.

Regards, Rob
"Bob Phillips" wrote in message
...
Courtesy of Tom Ogilvy

You use it like so

=showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32)

The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are
changed

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




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
I would like to show in a cell the criteria entered into a custom filter
of
data for example, if the custom criteria was set as = 3000 then either
enter 3000 in a cell or the text "greater than or equal to 3000"

Is this possible within a worksheet function? any examples would be most
welcome.

Thanks, Rob