Displaying autoFilter Criteria

[mark hansen]

I got the following code off of the internet and from what I read about it, it will display the criteria set in the autofilter for that column. I followed the directions but it doesn't work the way I thought it should. Which may be the problem, I may be looking at it the wrong way.

here is what I set up to test.
--Put a simple list of months in column A starting at A3 for about 10 rows or so.
--Went to View Code and inserted a Module. then pasted the code below in the module.
--In A1 enterd =FilterCriteria(A3)
--Selected A3 to the bottom of the list and set autofilter.

When I changed the filter criteria in column a, A1 didn't display the new criteria. It will display if I click in the formula bar and hit enter... then the filter criteria will display. But if I just change the auto filter - nothing.

My question, is SHOULD it just "refresh" the spreadsheet and update the new criteria, or is something like not that dramatic .

here is the code.
---------------------------------------
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
-------------------------------------------------------------

 

[Datsmart]

Try changing your formula to reference one cell lower. In your data it is assumed that A3 is the header of the column. In my test workbook it works fine if I reference A4.

 

[mark hansen]

That did, it... thanks for looking at this. I will find function helpful now that I use it.