Filter an Excel table based on criteria lists on different sheet. Click buttons to quickly run macros that apply an AutoFilter, or to clear all the filters. Get the free workbook
In a named Excel Table, the headings have drop down lists, AutoFilters, where you can select one or more items to filter the list.
Those drop down lists work well for manual filtering, but in some situations, you need a macro to do the filtering. This tutorial shows how to set up criteria lists for 2 fields in a named Excel table. Then, use those criteria lists in the macro, to apply filters to the table fields.
In the sample file for this workbook, there are the following sheets:
To see what type of code you need, record a macro while you manually apply filters to the Customer and Products fields in the named table, tblOrders, which is on the Orders sheet.
For this test macro, select 3 items in each filter:
Here is the code that Excel created, when I recorded a macro while manually applying those filters
NOTE: I added line breaks to the recorded code, to make the lines shorter
Sub FilterTableManualTest() ' ' FilterTableManualTest Macro Sheets("Orders").Select ActiveSheet.ListObjects("tblOrders").Range _ .AutoFilter Field:=2, Criteria1:= _ Array("FoodMart", "MiniMall", "VegiVille"), _ Operator:=xlFilterValues ActiveSheet.ListObjects("tblOrders").Range _ .AutoFilter Field:=4, Criteria1:= _ Array("Apples", "Bananas", "Grapes"), _ Operator:=xlFilterValues End Sub
Instead of typing an array of items in a macro, you can refer to a range on a worksheet. In this example, there is a worksheet named Admin, with two named Excel tables, where the criteria lists for Customer and Product can be entered. In each named table:
NOTE: You can't use wildcards with this technique, because that forces Excel to apply a Custom AutoFilter, "contains", which is limited to 2 criteria.
To add a new item in either table:
To remove items in either table:
On the Orders worksheet, there is a Filter button at the top of the sheet, to apply filters in the Customer and Product columns, based on the items in the criteria tables.
When you click that button, it runs the FilterRangeCriteria macro, shown in the next section.
Here is the FilterRangeCriteria macro that applies the filters in column 2 (Customer) and column 4 (Product).
Note: The Value2 property is used instead of Value (the default), to avoid problems if numbers are in the criteria lists.
Sub FilterRangeCriteria() Dim wsO As Worksheet Dim wsA As Worksheet Dim myOrders As ListObject Dim rngCust As Range Dim rngProd As Range Dim arrCust() As String Dim arrProd() As String Dim bCust As Boolean Dim bProd As Boolean Dim colCust As Long Dim colProd As Long On Error Resume Next Set wsO = Worksheets("Orders") Set wsA = Worksheets("Admin") Set myOrders = wsO.ListObjects("tblOrders") Set rngCust = wsA.Range("CustSel") Set rngProd = wsA.Range("ProdSel") bCust = rngCust.Cells.Count > 1 bProd = rngProd.Cells.Count > 1 colCust = 2 colProd = 4 If bCust Then arrCust = Split(Join(Application _ .Transpose(rngCust.Value2), "#"), "#") myOrders.Range.AutoFilter _ Field:=colCust, _ Criteria1:=arrCust, _ Operator:=xlFilterValues Else If rngCust.Cells(1).Value2 <> "" Then myOrders.Range.AutoFilter _ Field:=colCust, _ Criteria1:=rngCust.Cells(1).Value2, _ Operator:=xlFilterValues End If End If If bProd Then arrProd = Split(Join(Application _ .Transpose(rngProd.Value2), "#"), "#") myOrders.Range.AutoFilter _ Field:=colProd, _ Criteria1:=arrProd, _ Operator:=xlFilterValues Else If rngProd.Cells(1).Value2 <> "" Then myOrders.Range.AutoFilter _ Field:=colProd, _ Criteria1:=rngProd.Cells(1).Value2, _ Operator:=xlFilterValues End If End If End Sub
On the Orders worksheet, there is a Show ALL button, to clear the filters in the table.
When you click the button, it runs the ShowAllRecordsList macro, shown below.
NOTE: This macro clears all filters in the table, not just the ones that applied by the Filter button.
Sub ShowAllRecordsList1() 'shows all records in list 1, ' if filters applied Dim Lst As ListObject Set Lst = ActiveSheet.ListObjects(1) If Lst.AutoFilter.FilterMode Then Lst.AutoFilter.ShowAllData End If End Sub
To see the sample file from this tutorial, download the Table Criteria List workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.
Last updated: August 27, 2020 10:16 AM