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.

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

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 […]

How to use the Scroll Bar

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

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 […]