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.
- 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
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
How to use Excel Tables
Excel categories
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.