Displaying AutoFilter criteria

[lehainam]

Dear all,

I am using the below function to show the filter value. It works wel
for the first time filter. However, when I change the filter value, th
result of this function is unchanged until I go to the cell containin
this function and press F2 and then Enter, the result of this functio
is changed.

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

Is there any way that makes this function work without pressing F2 an
Enter when the value of filter is changed.

Thanks a lot!

Na

 

[Dave Peterson]

Tom Ogilvy has suggested this:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

Should trigger it to update. Change B5:B200 to reflect the address of a
column in the filtered database.

 

[GregR]

Dave, are suggesting adding this code to the function or using this to
call the function? TIA

Greg

 

[Dave Peterson]

The code wouldn't change.

But the formula in the cell would. (So I guess #2.)