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. Press with mouse 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 in this macro.

Nothing in the table is selected, see picture above.

Cell B4 is selected in the picture above and the Excel defined Table is instantly filtered based on cell B4.

This example shows cell C4 selected. Now are both values in cell B4 and C4 are now used as filtering parameters, simply press with left mouse button on a cell outside the Excel defined Table to clear filter conditions.

VBA Event code

The following Event code is rund every time a cell is selected.

'To use this Event the name must be Worksheet_SelectionChange(ByVal Target As Range)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Declare variables and data types
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim Scol As Single

'Enable error handling
On Error Resume Next

'Save cell to object ACell
Set ACell = Selection

Save Boolean value TRUE or FALSE to ActiveCellInTable  based on if Table name is empty or not.
ActiveCellInTable = (ACell.ListObject.Name <> "")

'Disable error handling
On Error GoTo 0

'Check if ActiveCellInTable equals to False meaning if selected cell is outside the Excel defined Table
If ActiveCellInTable = False Then

   'Go through ListObjects (Excel defined Tables)
   For i = 1 To ActiveSheet.ListObjects.Count

      'Iterate through columns 
      For j = 1 To ActiveSheet.ListObjects(i).Range.Columns.Count

          'Clear filter conditions for each column
          ActiveSheet.ListObjects(i).Range.AutoFilter Field:=j
      Next j
   Next i

   'Stop Event code
   Exit Sub
End If

'Make sure that only one cell is selected or stop event code
If Selection.Cells.Count > 1 Then Exit Sub

'Calculate relative column number in the Excel defined Table 
Scol = Selection.Column - ACell.ListObject.Range.Column + 1

'Apply filter condition to given column
ActiveSheet.ListObjects(ACell.ListObject.Name).Range.AutoFilter _
Field:=Scol, Criteria1:="=" & Selection

End Sub

Where to copy the code?

1. Press Alt+F11 to open the VB Editor
2. Double press with left mouse button on the sheet where the Excel defined Table is located in the project explorer window
3. Copy / Paste vba code to sheet module
4. Return to excel

Get the Excel file

Filter-a-table-using-selection-change-event.xlsm

Macro category

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Add values to a regular drop-down list programmatically
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]

Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Excel categories

Comments (2)