In this post I am going to demonstrate how to quickly apply a filter to a table. I am using the selection change event to apply the filter. Click on a cell in a table and the cell value is instantly used as a filter in the current table column.

Select a cell outside the table and all table filters are removed. Unfortunately, you can´t select multiple cells in a table column yet. That might become an upcoming post.

Nothing in the table is selected, see picture below.

Cell B4 is selected in the picture below.

Cell C4 is selected.

VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ACell As Range
    Dim ActiveCellInTable As Boolean
    Dim Scol As Single

    On Error Resume Next
    Set ACell = Selection
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0
    If ActiveCellInTable = False Then
        For i = 1 To ActiveSheet.ListObjects.Count
            For j = 1 To ActiveSheet.ListObjects(i).Range.Columns.Count
                ActiveSheet.ListObjects(i).Range.AutoFilter Field:=j
            Next j
        Next i
        Exit Sub
    End If

    If Selection.Cells.Count > 1 Then Exit Sub
    Scol = Selection.Column - ACell.ListObject.Range.Column + 1

    ActiveSheet.ListObjects(ACell.ListObject.Name).Range.AutoFilter _
    Field:=Scol, Criteria1:="=" & Selection

End Sub

Where to copy the code?

  1. Press Alt+F11
  2. Click a sheet in the project explorer window
  3. Copy / Paste vba code to sheet module
  4. Return to excel

Download excel 2007 *.xlsm file

Filter a table using selection change event.xlsm