Author: Oscar Cronquist Article last updated on September 10, 2012

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")
    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")
    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")
    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