Excel Filter Criteria List Macro

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

Excel Table Filters

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.

Workbook Setup

In the sample file for this workbook, there are the following sheets:

• Orders - has named table, tblOrders, with data validation drop down lists for Customer and Product
• Lists - has 2 named tables, tblCust and tblProd, and 2 named ranges, (CustList, ProdList), which are used in the drop down lists
• Admin - has 2 named tables, tblCustSel and tblProdSel, and 2 named ranges, (CustSel, ProdSel), which are used in the macro

Record a Macro

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:

• Customer is in column 2 of the table, and was filtered for FoodMart, MiniMall and VegiVille
• Product is in column 4 of the table, and was filtered for Apples, Bananas and Grapes

Recorded Macro Code

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

• The Orders sheet was selected
• An array with 3 Customer names was used to filter Field 2
  • Array("FoodMart", "MiniMall", "VegiVille
• An array with 3 Product names was used to filter Field 4
  • Array("Apples", "Bananas", "Grapes"
• The xlFilterValues Operator was used for both filters, because more than 2 items were selected

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

Criteria Tables

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:

• Column 1 has a formula that calculates a number sequence
• Column 2 has drop down lists where you can select criteria items

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.

Add an Item

To add a new item in either table:

• Select the last cell in the table
• Press the Tab key, twice, to start a new row, and to move to the 2nd column.
• Select an item from the drop down list

Remove Items

To remove items in either table:

• Select the rows that you want to remove
• In one of the selected rows, right-click a cell
• In the popup menu, point to Delete, then click Table Rows

Apply the AutoFilters

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.

• The macro checks the criteria tables, to see if there are items listed
• If there are multiple items, the macro creates an array of those items
  • Then the macro applies an AutoFilter, using that array
• If there is only one item in the criteria list, the macro filters for that item

AutoFilter Macro

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

Clear the Filters

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.

• The macro checks the table, to see if an AutoFilter has been applied.
• If so, the macro shows all the data.

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

Download the Sample File

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.

More Tutorials

AutoFilter Basics

Excel Named Table

AutoFilter Tips

Excel List AutoFilter Macros

Worksheet AutoFilter Macros