Author: Oscar Cronquist Article last updated on February 01, 2019

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro.

How it works

  1. Type a value in cell range B2, C2 or D2.
  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. Copy above VBA macro (CTRL + c)
  2. Press Alt+F11 to open the Visual Basic Editor.
  3. Click "Insert" on the menu.
  4. Click "Module" to create a module.
  5. Paste code to module (CTRL + v)
  6. Exit VBE and return to Excel.

How to create a button

  1. Click "Developer" tab.
  2. Click "Insert" button.
  3. Create a button (Form Control).
  4. Click and drag on your worksheet to create the button.
  5. Right click on your new button.
  6. Select a macro to assign.
  7. Click OK button.

The assigned macro is executed when the user clicks on the button.

Download Excel file


Dynamic-filtering-excel-tables.xlsm

Related articles

Copy Excel Table filter criteria programmatically

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another [โ€ฆ]

Hide specific columns programmatically

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also [โ€ฆ]

Scroll through a data set [VBA]

Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. [โ€ฆ]

Copy filtered Excel tables [VBA]

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is [โ€ฆ]