One of my 12 fans (Lester) emailed to remind me that I have a blog. I almost forgot. So with a good kick in the pants, I'm back to blogging.
.
While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Here we go.
.
Today, I'll show you a nifty trick that allows you to actually see the AutoFilter criteria you have applied on an Excel table.
.
This is actually a pretty common request. When you've got an AutoFilter applied, it would often be nice to see how the table is actually AutoFiltered. Alas, Excel's only accommodation is to give you a generic count of records on the status bar.
.
.
I've come up with a piece of code that will give you more details in the status bar. Like this:
.
.
Here is how you can implement this:
Step 1:
Copy and paste this 'ShowAutoFilterCriteria' Macro into a standard module.
-
Sub ShowAutoFilterCriteria()
-
Dim AF As AutoFilter
-
Dim TargetFilter As Filter
-
Dim TargetField As String
-
Dim strOutput As String
-
Dim i As Integer
-
-
'Check if the sheet is not auto-filtered, then exit
-
If ActiveSheet.AutoFilterMode = False Then
-
Application.StatusBar = False
-
Exit Sub
-
End If
-
-
Set AF = ActiveSheet.AutoFilter
-
-
'Loop through the Filters of the AutoFilter
-
For i = 1 To AF.Filters.Count
-
TargetField = AF.Range.Cells(1, i).Value
-
-
'Get the Filter object. If it is on, then get the standard filter criteria
-
Set TargetFilter = AF.Filters(i)
-
If TargetFilter.On Then
-
-
'Avoid error when more than two filters are applied
-
On Error GoTo CriteriaOverflow
-
strOutput = strOutput & " " & TargetField & TargetFilter.Criteria1
-
-
'Account for special filters
-
Select Case TargetFilter.Operator
-
Case xlAnd
-
strOutput = strOutput & " And " & TargetField & TargetFilter.Criteria2
-
Case xlOr
-
strOutput = strOutput & " Or " & TargetField & TargetFilter.Criteria2
-
Case xlBottom10Items
-
strOutput = strOutput & " (bottom 10 items)"
-
Case xlBottom10Percent
-
strOutput = strOutput & " (bottom 10%)"
-
Case xlTop10Items
-
strOutput = strOutput & " (top 10 items)"
-
Case xlTop10Percent
-
strOutput = strOutput & " (top 10%)"
-
End Select
-
End If
-
SkipItem:
-
Next
-
-
'Display the filters if there are any
-
If strOutput = "" Then
-
Application.StatusBar = False
-
Else
-
Application.StatusBar = strOutput
-
End If
-
Exit Sub
-
-
-
'Error handler when more than two filters are applied
-
CriteriaOverflow:
-
strOutput = strOutput & " " & TargetField & "= Multiple Filters"
-
ErrorHandler: Resume Next
-
-
End Sub
.
Step 2:
In the Workbook's SheetCalculate event, enter a call to the 'ShowAutoFilterCriteria' macro.
.
Step 3:
Be sure you enter a volatile formula in any dummy cell on the sheet. This will trigger the Workbook_SheetCalculate event. For example, I use the Now() function. This ensures that when I change the Autofilter, the macro will fire automatically.
.
If all went well, you'll have a jazzy way to see the criteria you've applied to your AutoFilters.
.
Sweet!
p.s. - Thanks, Lester! (lol!)
Congratulations and best wishes on the MVPS title recredit/renewal.
Dang -- I asked for an Access tip!
LOL -- Just kidding. Thank you, Mike.
Cool and quite useful - thanks for sharing!
The text in the status bar persists when the spreadsheet is closed or another opened. You need to clear it using the Workbook Deactivate or Close event.
I implemented this in a workbook that needed it, and noticed an interesting side effect. In my case it was beneficial, but it might not be for some.
You use the NOW() function on the worksheet in question, along with that worksheet's Calculate event. Turns out other autofilters on other worksheets ALSO trigger the NOW() function on the original worksheet, and thus the Calculate event. So setting this up on one worksheet in effect sets it up on EVERY worksheet!
Good idea Mike
Cool trick. Is there anyway to show the search criteria in addition to the number of results? I like the idea of seeing the search criteria, but I reference the number of records quite a bit, so wouldn't want to give it up.
In the line above NEXT command you use a tag SKIPITEM: that is not used for a macro.
In the line:
'Avoid error when more than two filters are applied
On Error GoTo CriteriaOverflow
On the line where you have put "CriteriaOverflow" wouldn't "SkipItem" ?
Very clever tip ... !!!
Extremely handy when dealing with large data sets ...
Could this be setup to work with:
1. the Sub ShowAutoFilterCriteria() is stored only in a module of my Personal.xlsb file
2. the Call method added to the Workbook_SheetCalculate event of the ThisWorkbook object of the target workbook, and
3. =Now() included in a cell on the target worksheet
so that I don't have to add the Sub to each target workbook?
Colin, I had the same question as you regarding putting this in my personal macro workbook.
You can do this in the Workbook / SheetCalculate call.
Application.Run "personal.xls!ShowAutoFilterCriteria"
In other words "FILENAME-where-module-is-stored!MACRO-NAME"
thanks DP for the tip, I am sharing this with some co-workers as well.
-Chris
I tweaked it a little to have it called from the Workbook_SheetSelectionChange event, 'cause my engineers got too confused when it showed a filter that was on a different sheet... (sigh...)