Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)

The AutoFilter function is one of several Excel features that may also be utilized in VBA. If you have a dataset and wish to filter it based on a cell value, the Filter option on the Data ribbon makes it simple. In this tutorial, we will show you how to filter a table based on a cell value with Excel VBA.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Handy Approaches to Filter Table Based on Cell Value with Excel VBA

We’ve provided an example data set in the figure below, from which we’ll filter values based on specified cell values. With Excel VBA, we’ll filter results based on Text Conditions, Multiple Criteria (AND/OR), and Cell Values.

1. Filter Table Based on a Text Condition with Excel VBA

To filter a table based on a Text Condition simply follow the steps below.

Steps:

• Firstly, to start a VBA Macro, press Alt + F11.
• Click on the Insert tab.
• Select a Module.

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

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

Here,

• Field: =2 is the second column.
• The first criterion is Criteria1 = Beef.

• Finally, save the program and press F5 to run it.

Read More: How to Use Text Filter in Excel (5 Examples)

2. Filter Table Based on Cell Value with Multiple Criteria

With the help of Excel VBA, we can apply filters for multiple AND or OR criteria. To do so, follow the outline steps in sections.

2.1 Based on OR Criteria

Steps:

• After selecting a new Module, paste the following VBA codes.

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

• Therefore, save the program and press F5 to run the program.

2.2 Based on AND Criteria

Steps:

• In a new Module, paste the following VBA codes for applying the AND criteria.

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.

• Finally, press F5 to run the program after saving.

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

3. Filter Table Based on Multiple Criteria with Different Columns

In the previous method, we applied multiple criteria in the same column. Moreover, we can filter for multiple criteria in different columns. Follow the outlined instructions to accomplish the task.

Steps:

• From the Insert tab, select a new Module.
• Then, copy and paste the following VBA codes.

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

• Finally, save the program and run it by pressing F5.

Read More: How to Filter Multiple Columns in Excel Independently

Similar Readings

• How to Use Filter in Protected Excel Sheet (With Easy Steps)
• Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
• How to Filter Data in Excel using Formula
• Excel Filter Data Based on Cell Value (6 Efficient Ways)
• How to Filter Multiple Columns Simultaneously in Excel (3 Ways)

4. Apply AutoFilter in VBA Code to Filter Top 5 Records in a Table

To extract the top 5 values based on cell values, follow the procedures below.

Steps:

• Firstly, select a new Module from the Insert tab.
• Then, copy and paste the following VBA codes for filtering the top 5 Sales values.

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

• Therefore, to get the result, first save and then press F5 to run.

Read More: VBA Code to Filter Data in Excel (8 Examples)

5. Use AdvancedFilter in VBA Code to Filter Table Based on a Cell Value

We can dynamically enter values and filter results with Excel VBA. Follow the instructions below to extract values based on a specific cell value.

Steps:

• From the Insert tab, select a new Module.
• Paste the following VBA codes.

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

• Finally, to run the program press F5 after making sure that you have saved the program.
• Therefore, enter a value in cell F5 (Chicken), then all the values with the Chicken will be filtered.

Read More: Excel VBA: Filter Based on Cell Value on Another Sheet (4 Examples)

6. Apply Wildcard Characters to Filter 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. Follow the procedures indicated below to complete the task.

Steps:

• Firstly, select a new Module from the Insert tab.
• Then, copy and paste the following VBA codes.

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

• Therefore, run the program after saving it and see the result as shown in the image below.

Read More: How to Filter Excel Pivot Table (8 Effective Ways)

Conclusion

To conclude, I hope this article has given you some useful information about how to filter a table based on a cell value with Excel VBA. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.

Related Articles

• How To Search Multiple Items in Excel Filter (2 Ways)
• Filter Horizontal Data in Excel (3 Methods)
• How to Perform Custom Filter in Excel (5 Ways)
• Filter by Date in Excel (4 Quick Methods)
• How to Filter by Color in Excel (2 Examples)
• How to Copy and Paste When Filter Is Applied in Excel