I was just about to give up when I had an idea.
The problem is that by virtue of the grid, my previous solution of comparing
the visible cells against the cells with values would always be the same, it
is either X or nothing.
My brainwave was remembering a UDF that Tom Ogilvy posted a while back,
shown at the end. The way to use it is to select all the columns and add a
CF formula of =ShowFilter(B1), assuming the CF starts in column B. Big
problem is that it is slow running the UDF against whole columns. A
compromise is to add a row before the existing row 2 with this formula in B2
=showfilter(B1)&CHAR(SUBTOTAL(9,B$3)*0+32)
and copy across. Your CF formula then becomes =LEFT(B$2,2)="No", which seems
acceptable response. You can even hide the new row 2 and it still works
fine. Kinda cool IMO
)
Tom's UDF
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"
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"
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)
