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

Related posts:

Sort values in a table (vba)

Select a cell in a table and the chart updates automatically (vba)

Quickly filter a column in an excel table

Copy excel table filter criteria (vba)

Use a calendar to filter a table