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.
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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.
English
Spanish
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.