Filter an Excel defined Table programmatically [VBA]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro.
How it works
- Type a value in cell range B2, C2 or D2.
- 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?
- Copy above VBA macro (CTRL + c)
- Press Alt+F11 to open the Visual Basic Editor.
- Click "Insert" on the menu.
- Click "Module" to create a module.
- Paste code to module (CTRL + v)
- Exit VBE and return to Excel.
How to create a button
- Click "Developer" tab.
- Click "Insert" button.
- Create a button (Form Control).
- Click and drag on your worksheet to create the button.
- Right click on your new button.
- Select a macro to assign.
- Click OK button.
The assigned macro is executed when the user clicks on the button.
Weekly Blog 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 […]
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 […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
2 Responses to “Filter an Excel defined Table programmatically [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Well, it’s a very good sign to develop an excel software. I do agree with you with the advantages of this package :)
Exactly what I have been searching for, perfect example.
I have two requests.
1. How do I create a clear data button, ready for the next search.
2. Can the search criteria in any field by partial. i.e. If I type just the letter T or TA in the Company search box it shows me all companies with the letter T or Ta in it's name.
Thanking you in advance.