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.
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?
- Press Alt+F11
- Click a sheet in the project explorer window
- Copy / Paste vba code to sheet module
- Return to excel
Download excel 2007 *.xlsm file