Filter an Excel defined Table programmatically [VBA]
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
- 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.
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module" to create a module.
- Paste code to module (CTRL + v)
- Exit VBE and return to Excel.
How to create a button
- Press with left mouse button on "Developer" tab.
- Press with left mouse button on "Insert" button.
- Create a button (Form Control).
- Press with left mouse button on and drag on your worksheet to create the button.
- Press with right mouse button on on your new button.
- Select a macro to assign.
- Press with left mouse button on OK button.
The assigned macro is rund when the user press with left mouse button ons on the button.
Table category
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 […]
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 in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
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 […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Macro category
Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
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 […]
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 […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
How to use Excel Tables
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.