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.
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.
.
Here's a sample file if you want to see it in action.
RELATED STUFF
- Mocking the ‘Merge & Center’ Icon
- Highlighting the Active Row and Column
- Why Replace the DOM Interface
- Running an Access Parameter Query from Excel
- Fun with Custom Lists
- Sort by Color in Excel 2003
- Auto Adjust Chart Label Positions
- Recordset Tricks in Excel 1 – Filling a ComboBox with Unique Values
- Running Crosstab Queries in Excel
- Status Bar Fun – Show Cell Selection Info
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...)