Click a Cell to Filter Excel Column

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.

.filterclick01

To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.

filterclick02

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”.

filterclick03

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.

_______________

You may also like...

4 Responses

  1. Wanda says:

    I have a excel 2007 spreadsheet that has 7 row and 10 colums. 5 of those columns has a multiple select dropdown list and the dropdown list has the ability to select multiple items. Thank you Contextures! The problem I am having is that, when I protect the spreadsheet and lock down column C, where I have the (=TODAY())CODE, the spreadsheet looses the ability to multi-select from the dropdown lists.

    • @Wanda, the code should work, even if the sheet is protected. Is there other code on the worksheet module? Maybe there’s another line that isn’t able to run, and it’s exiting the procedure before it gets to the multi-selection code.

  2. Jarrad says:

    Debra,
    This code is amazing! Looking forward to adding it to future reports and looking like a excel wizard.
    Thanks!

  3. FrozenConicBeans says:

    Hi,
    this is amazing but i have a question: is it possible clicking a cell in one sheet, to filter the values in another sheet?

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *