Displaying autoFilter Criteria

mark hansen

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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
That did, it... thanks for looking at this. I will find function helpful now that I use it.
 

Forum statistics

Threads
1,181,102
Messages
5,928,067
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top