In this tutorial I am going to demonstrate how to filter an excel table very quickly. There is not much vba code to make this possible.

How it works

  1. Type a value in cell range A2, B2 or C2.
  2. Press Filter button

The table column below is instantly filtered. If the cell value is empty, no filter is applied.

VBA code

Sub TblFilter()
If Worksheets("Sheet1").Range("A2") <> "" Then
    Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, Criteria1:="=" & Worksheets("Sheet1").Range("A2")
Else
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
End If
If Worksheets("Sheet1").Range("B2") <> "" Then
    Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=2, Criteria1:="=" & Worksheets("Sheet1").Range("B2")
Else
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
End If
If Worksheets("Sheet1").Range("C2") <> "" Then
    Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=3, Criteria1:="=" & Worksheets("Sheet1").Range("C2")
Else
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
End If
End Sub

Where to copy vba code?

  1. Press Alt+F11
  2. Insert a new module
  3. Copy paste code into code window

How to create a button

  1. Click "Developer" tab
  2. Click "Insert" button
  3. Create a button (form control)
  4. Right click on the button
  5. Assign your macro

Download excel 2007 MacroEnabled *.xlsm file

Dynamic filtering excel tables.xlsm