AutoFiltering with a Macro and a Keyboard Shortcut

In order to make the filtering easier to use, there is a keyboard shortcut Ctrl-Shift-A that calls a macro:

Here’s the code of the macro AutofilterUpdate that is called:

Sub AutofilterUpdate()
 If Selection.Cells.Count = 1 Then
     If Selection.Value <> "" Then
         Selection.AutoFilter
         Selection.AutoFilter
     End If
 End If
 Selection.AutoFilter Field:=ncRelevance, Criteria1:=scRelevanceCurrent
End Sub

First (lines 2 & 3), this code checks if the selection is within the list of items and only one cell is selected. (Otherwise, the call to Selection.AutoFilter will cause a strange runtime error.) This requires two ifs because I could not find a way to “short-circuit” the evaluation.

Then there are two calls to Selection.AutoFilter (lines 4 & 5). This extends the reach of the autofilter to newly added items in the list.

Finally, in line 8 the filter is set to only select those items that have a relevance (as calculated with the User Defined Function) of “current”. This simple code takes care of “removing” old items from your view without deleting them. Next: Sending an EMail.