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.

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

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: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)

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: How to Filter Based on Cell Value Using Excel VBA

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: Excel VBA: How to Filter with Multiple Criteria in Array

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: How to Remove Filter in Excel VBA

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 to Filter in Same Column by Multiple Criteria

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.

Download Practice Workbook

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

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.

Stay with us & keep learning.

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