Filter Condition

[Guest]

Hi,

I have a spreadsheet that allows inexperienced users do their own analysis
on a large table of data by using filtering and SUBTOTAL().

I would really like to show close the the final results what choice(s), i.e.
the filter condition(s), that have been selected to produce the results.
This will enable them to print out the results and know at a later date what
the figures represent.

Is there any way of determining, by function or VBA, the filter condition of
each column?

Thanks,
Fred

 

[Bob Phillips]

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

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Fred;

Why not just use the autofilter. Select a cell in your data and apply the
autofilter. Filter any criteria with the autofilter, except the columns that
you are summing. One cell below the subtotal click sum(). Select the entire
column of data, not the entire column. When you filter the data the sub total
will change automatically.

God Bless

Frank Pytel