get-digital-help.com

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 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 click a cell outside the Excel defined Table to clear filter conditions.

VBA Event code

The following Event code is executed 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 click 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