How to set the active cell’s value as the filter criteria in Microsoft Excel Tables by double clicking
In a post on the Excel Hero LinkedIn Group Charles asked today:
“[…] I wish to click on a cell with a purchase order number and have that action filter the list based on that purchase order number.
What could be a strategy please? […]”
In one of the answers to Charles’ question, Craig pointed to the most obvious solution: right click and select Filter | Filter By Selected Cell’s Value. Well, I know this is available in Excel 2010, but I can’t tell you no lies: I don’t even remember if this feature was available already in 2003 and earlier.
Anyway. Although at least Excel 2010 provides a built-in feature for filtering by the value of the active cell, Charles' idea is very interesting and reminded me of a post we had here last year: Sort Excel Tables by Double Clicking.
The article provided a small VBA code snippet to sort data tables by double clicking into any given column. The technique used back then for sorting can easily be adjusted to implement Charles’ original idea: filter data tables by the value of the active cell after double clicking and remove the filter criteria with another double click.
The implementation is simple: all you need is to define a name for the range where your data is (e.g. “myData”) and a very small piece of VBA code (24 lines of code) in the event driven Worksheet_BeforeDoubleClick event procedure.
- checks if the the user double clicked inside of the data
- detects the active column
- checks if the auto filter is set and sets it, if not
- checks if a criteria is defined already and if so, removes the criteria or otherwise sets the criteria to the value of active cell
If you are interested in the details, have a look at the workbook provided for download below. If you have any questions, please leave me a comment.
A simple and effective way of setting / removing filter criteria by double clicking. The technique is not limited to one column. You can also select several columns to filter your data.
Here is the workbook for free download:
You can even combine the filtering by double clicking with the sorting by double clicking technique, if you change the VBA to sort only if the user double clicks on the header of a column. A simple IF clause and a combination of the two code snippets will do the job.