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.

Sample Data

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.

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

  • 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.

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

Read More: How to Filter Multiple Columns in Excel Independently


Similar Readings


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

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

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

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

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

Sample Data

  • 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.

Sample Data

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

Sample Data

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

Sample Data

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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. This page is all about filtering a table and yet no where do you actually refer to the table name or columns.

    Surely the whole benefit of using a table is you do not need to have hard-coded column/row references, e.g
    Range(“B4:D11”).CurrentRegion.AdvancedFilter…

    You can just use the table name:
    [tblMyTable].CurrentRegion.AdvancedFilter…

    Or:
    ActiveSheet.ListObjects(“tblMyTable”).databodyrange.AdvancedFilter…

    Then if anything changes in your table (ie new data rows are added) your code still works.

Leave a reply

ExcelDemy
Logo