Susan said:
I have Excel 2007 version and I was sent a spreadsheet that was filtered.
I can clear the filter but is there an easy way to see what filter criteria
was used or is it a matter of trial and error to see what the person did.
Don't have/know xl2007
but think you could try Tom Ogilvy's ShowFilter UDF (below)
To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF (below) into the white space on the right
[everything within the dotted lines]
Press Alt+Q to get back to Excel
In Excel, assuming autofilter is applied on cols A to C in row1
To use Tom's UDF, put in say, K1: =showfilter(A1), copy K1 to M1.
K1:M1 will display the autofilter selections made in A1:C1
'---
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
Application.Volatile
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
'---
---
