Excel Column Filter tools

Description
UPDATE May 2021: I have previously worked with pre-Excel 2007 'data ranges', so I hadn't worked with ListObject Tables until recently. I've discovered that my utilities didn't work with these. So, I've now updated them so that they all work both on old-fashioned data ranges and the newer ListObject Tables. I've also added a handy utility called ResizeTable that resets the Table name to the exact data range (in case something gets out of sync). Everything else is as before: just download the VBA code from the revised MSWord file, 'Column Filter Macro text - works on Tables.docx'.

These 3 Excel VBA macro utilities enable you to:-
a) quickly apply a CUSTOM filter criterion to a column;
b) clear a single column filter; and
c) filter a column on its current value
without all the keystrokes these actions usually require.

If you frequently work with large Excel data tables (like I do) and have a recurring need to apply filters to different columns, to analyse or code up your data (particularly your own CUSTOM filter criteria), then you’ll know that it can be tedious using the standard Excel filter facilities.

The functionality within Excel's built-in ‘Custom filter…’ option is certainly handy, allowing various Boolean comparisons and nesting of criteria, for more complex queries. But if, most of the time, you just want to filter records that contain a single piece of text (that you specify), it can be very tedious, particularly if you want to keep doing it for various different values. You have to select the drop-down arrow at the top of the column, possibly have to click on another sub-menu (depending on the Excel version you’re using) then skim down to the ‘Custom filter…’ option. Then you still have to type in the text you’re searching on and, if it isn’t an exact match you’re after but, rather, any record that contains the text you type, you still have to click on the drop-down box then drag the slider down before you’re able to select the ‘contains’ option (or, alternatively, you have to type an asterisk both before and after your search text).

1. The first of these macros allows you to do that much quicker!
2. The second macro allows you to clear any column filter in a single click (instead of 3 or more).
3. The third allows you to filter on the current cell’s value (without having to type it).
[Earlier versions of Excel did this if you just clicked on the Auto-Filter icon, which was handy. The problem was, there was no easy way to clear all filters if you wanted to. In later versions, the Auto-Filter icon now works to toggle filters on and off, so it’s lost the facility to filter on the current cell’s value.]

The macro text is in the attached 'Column Filter Macro text.docx' file: you can just copy the text then paste it into your favourite place in your own VBA library (e.g. the Modules of your Personal.xlb file). The VBA macros ‘FilterOnColumn’, ‘ClearFilterOnColumn’ and ‘FilterOnCurrentValue’ will then be available to run.