mark hansen
Well-known Member
- Joined
- Mar 6, 2006
- Messages
- 534
- Office Version
- 2016
- Platform
- Windows
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
-------------------------------------------------------------
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
-------------------------------------------------------------