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
'Stop Event code
'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
Field:=Scol, Criteria1:="=" & Selection
Where to copy the code?
Press Alt+F11 to open the VB Editor
Double click the sheet where the Excel defined Table is located in the project explorer window