How to Filter a Table based on Cell Values with Excel VBA (6 Methods)

Dataset Overview

We’ve included a sample dataset in the figure below, from which we’ll sift through values according to specific cell criteria. Using Excel VBA, we’ll sort out results based on text conditions, multiple criteria (both AND and OR), and cell values.

Method 1 – Filtering a Table Based on a Text Condition with Excel VBA

To filter a table based on a Text Condition, follow these steps:

• Initiate a VBA Macro by pressing Alt + F11.
• Click on the Insert tab.
• Select Module.

• Then, paste the following VBA codes into the Module:

Sub Text_condition()
Worksheets("Sheet1").Range("B4").AutoFilter Field:=2, Criteria1:="Beef"
End Sub

In this code:

• The “Field:=2” indicates the second column.
• The first criterion, “Criteria1:=Beef,” filters for the text “Beef.”

• Save the program and press F5 to execute it.

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)

Method 2 – Filtering a Table Based on Cell Value with Multiple Criteria

With Excel VBA, you can apply filters for multiple AND or OR criteria. Here’s how:

2.1 Based on OR Criteria

• Select a new Module and paste the following VBA code:

Sub Multiple_Criteria_OR()
Worksheets("Sheet2").Range("B4").AutoFilter Field:=2, Criteria1:="Beef", Operator:=xlOr, Criteria2:="Chicken"
End Sub

• Save the program and press F5 to run it.

2.2 Based on AND Criteria

• In a new Module, paste the following VBA code:

Sub Multiple_Criteria_AND()
Worksheets("Sheet2").Range("B4").AutoFilter Field:=3, Criteria1:=">500", Operator:=xlAnd, Criteria2:="<2000"
End Sub

Here,

• The first criterion is a Sales value greater than 500.
• Second Criterion is the Sales value of less than 2000.

• Save the program and run it by pressing F5.

Read More: How to Filter Based on Cell Value Using Excel VBA

Method 3 – Filtering a Table Based on Multiple Criteria with Different Columns

In this method, we’ll apply multiple criteria in different columns:

• Select a new Module from the Insert tab.
• Copy and paste the following VBA code:

Sub Multiple_Criteria_With_Different_Columns()
With Worksheets("Sheet3").Range("B4")
.AutoFilter Field:=2, Criteria1:="Chicken"
.AutoFilter Field:=3, Criteria1:=">1000"
End With
End Sub

• Save the program and run it by pressing F5.

Read More: Excel VBA: How to Filter with Multiple Criteria in Array

Method 4 – Applying the AutoFilter in the VBA Code to Filter Top 5 Records in a Table

To extract the top 5 values based on cell values:

• Select a new Module from the Insert tab.
• Copy and paste the following VBA code:

Sub Top_5_Records()
ActiveSheet.Range("B4").AutoFilter Field:=3, Criteria1:="5", Operator:=xlTop10Items
End Sub

• Save the program and run it by pressing F5.

Read More: How to Remove Filter in Excel VBA

Method 5 – Using AdvancedFilter in the VBA Code to Filter Table Based on a Cell Value

You can dynamically enter values and filter results with Excel VBA:

• Select a new Module from the Insert tab.
• Paste the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = Range("F5").Address Then
       Range("B4:D11").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("F4:F5")
   End If 
End Sub
Sub BasedOnCellValue()
End Sub

• Save the program and run it by pressing F5.
• Therefore, if you enter a value in cell F5 (Chicken), then all the values with the Chicken will be filtered.

Read More: Excel VBA to Filter in Same Column by Multiple Criteria

Method 6 – Applying Wildcard Characters to Filter a Table Based on a Cell Value

Wildcard characters (*) can be used to filter values in a cell that include a certain part. When we type Chicken in between wildcard characters, for example, it will extract all values containing Chicken.

• Select a new Module from the Insert tab.
• Copy and paste the following VBA code:

Sub Using_Wildcard_Characters()
Worksheets("Sheet6").Range("B4").AutoFilter Field:=2, Criteria1:="*Chicken*"
End Sub

• Run the program after saving it.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel VBA: Filter Based on Cell Value on Another Sheet
• VBA Code to Filter Data in Excel
• Filter Different Column by Multiple Criteria in Excel VBA