Whenever possible I employ lookups, crosswalks and whining to avoid correcting data by hand. Sometimes my best efforts fail, and I end up fixing lists one funky cell at a time. The horror! Happily I’ve created FilterMatic to ease the pain.
Say for instance, you’re giving a birthday party for a hundred or so of your oldest friends, all born on October 26. You’ve got this handy reference list of names, years of birth and notable accomplishment:
source:http://www.historyorb.com/today/birthdays.php
Meanwhile you’ve got another, very similar, list that calculates their age – with a lookup to the “year” column on the first list – so you can embarrass them by putting it on their place setting. The problem is some names are misspelled and you’re getting #N/A’s.
So you filter to just the #N/A’s and start to fix them. Wouldn’t it be nice if, as you fixed the errors, they were automatically filtered away? It’s true that in modern Excel tables you can do this with right-click>Filter>Re-Apply. But now with FilterMatic your corrections are instantly whisked from sight!
In the example below, I’m fixing the names, at first without FilterMatic running. After the third fix, I start it up. The first thing that happens is the filter is re-applied. After that the filter is re-applied with every change to the table and each fixed row is filtered away.
FilterMatic™ works on worksheet and table filters, re-applying them on the active sheet whenever you change a cell within a filtered area. And with FilterMatic™ there’s no confusing buttons or messy dropdowns. Just turn it on to start filtering! Close it when you’re done! But wait, there’s more! Download now and receive a free puppy!
Here’s the code:
Private WithEvents wsActive As Excel.Worksheet
'My ShowModal Property must be set to False
Private Sub UserForm_Activate()
Set app = Application
If Not ActiveSheet Is Nothing Then
Set wsActive = ActiveSheet
FilterMatic
End If
End Sub
Private Sub app_SheetActivate(ByVal Sh As Object)
Set Sh = wsActive
End Sub
Private Sub wsActive_Change(ByVal Target As Range)
FilterMatic
End Sub
Private Sub lblFilterMatic_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'apply changes by double-clicking the form
FilterMatic
End Sub
Sub FilterMatic()
Dim lo As Excel.ListObject
Set lo = Selection.ListObject
'if the selection overlaps a table
If Not lo Is Nothing Then
With lo
'Table is in filter mode
If .ShowAutoFilter Then
.AutoFilter.ApplyFilter
End If
End With
Else
'It will only re-apply a worksheet-level filter if
'there's no tables on the sheet.
With wsActive
'if sheet is filtered
If .FilterMode = True Then
'if the selection overlaps the worksheet's filtered area
If Not Intersect(Selection, .AutoFilter.Range) Is Nothing Then
.AutoFilter.ApplyFilter
End If
End If
End With
End If
End Sub
When the form is opened an application object is created. This object monitors all “SheetActivate” events in Excel and sets the activated worksheet to wsActive. There are three events that trigger the filtering code: opening the form, changing something in wsActive or clicking the label in the center of the form.
The FilterMatic sub checks for both ListObject and worksheet-level filters. If the selection at the time of the change intersects one, then that filter is updated. Note that if a listobject is found, it won’t go on to re-apply a worksheet filter. I tried that and couldn’t imagine a reasonable worksheet design that would have both filter types, and anyways the results were weird. Also note that if you change two listobjects at once, the filter would only be applied to one of them: whichever is the Selection.ListObject.
Here’s a workbook with the code and the lists.
And be sure to join the email list so you won’t miss my next exciting product: PivotMasher!
I don’t think that code is in that workbook. Or I’m missing something obvious.
Thanks Dick! It should be good now.
Perfect, thanks. I’m definitely adding this to PMW. It needs be a ribbon toggle rather than userform, but I can see myself using this a bunch.
I’m glad to hear it. I agree completely about the ribbon toggle.
Your puppy is in the mail.
When this is turned on, does one lose the undo feature of excel?
Yes, you lose undo as soon as it reapplies a filter.
I used to worry more about losing the undo stack, and I certainly wish it didn’t happen when VBA is executed. But honestly, I don’t recall any recent event where losing the Undo list has hurt me. I do save very often, and do pretty regular backups of complex workbooks.
And especially with this tool which is designed to ease the tedium and inaccuracy of a manual process, I’m not to worried about committing some big error that would be hard to recover from.
Hey, that’s cool. Another approach: Whip up a Pivot of the sample data, and drag just the column of interest into the Report Filter field. Users can then do the exact same thing, with no code. You can then either point a dynamic name at the pivot, or read the contents with some VBA.
Thanks, Jeff. I always appreciate your thoughts, but I have no idea how your suggestion accomplishes the same thing. If I drag just the Person column into the Report Filter field I get an empty pivot table.
Also, did I mention that the idea was to make data entry simpler? 🙂
I have no idea two years later how this helps either. I wonder if I meant to put this comment in another post of yours altogether. =RAND() !