Click on a cell in an Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.
To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.
FilterClick Sample File
This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet . You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.
The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.
Update for Working With Excel Tables
I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.
In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.
- Set rngF = ActiveSheet.ListObjects(1).Range ‘for tables
- ‘Set rngF = ActiveSheet.AutoFilter.Range ‘for AutoFilter ranges
Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.
You can see more examples of List AutoFilter VBA here.
The FilterClick Code
Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngF As Range Dim rngFS As Range Dim lRow As Long Dim lCol As Long Set rngF = ActiveSheet.ListObjects(1).Range 'for tables 'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges Set rngFS = ActiveSheet.Range("FilterStatus") lCol = rngF.Columns(1).Column - 1 lRow = rngF.Columns(1).Row If Target.Count > 1 Then GoTo exitHandler If Target.Address = rngFS.Address Then If rngFS.Value = "On" Then rngFS.Value = "Off" Else rngFS.Value = "On" End If End If If UCase(rngFS.Value) = "ON" Then If Not Intersect(Target, rngF) Is Nothing Then If Target.Row > lRow Then rngF.AutoFilter Field:=Target.Column - lCol, _ Criteria1:=Target.Value ElseIf Target.Row = lRow Then rngF.AutoFilter Field:=Target.Column - lCol End If End If End If exitHandler: Exit Sub End Sub
Download the Sample File
You can download the sample file, with the code for both versions – table and non-table – on my Contextures website. Go to the Excel Sample Files page, and in the Filters section, look for FL0021 – FilterClick.