Click a Cell to Filter Excel Column

Here is a fancy filter trick, if you frequently have to filter your Excel tables. Click on a cell in this 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.

.

Clear the Filter

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.

_______________