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

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 4 different examples to filter a dataset based on a cell value that is on another sheet using VBA code in Excel. We’ll use Excel’s built-in Range.AutoFilter method to illustrate these examples using its different properties.


Filter Data Based on Cell Value on Another Sheet Using VBA in Excel: 4 Examples

Let’s introduce our dataset first. Here we have a list of sale details for 4 different products from 2 categories. We want to filter this dataset based on a cell value that is on another sheet.

Introduction to the Range.AutoFilter Method

In the following examples, we’ll use the Range.AutoFilter method in our VBA code to filter a dataset using the AutoFilter. The method has several arguments to operate with. The syntax is-

expression.AutoFilter(Field, Criteria 1, Operator, Criteria 2, SubField, VisibleDropDown)

In the above arguments-
Field– We need to put an integer offset of the field to set the filter on the dataset. The count starts from the leftmost side of the dataset as one.

Criteria 1 – We can use “=” for blank cells, “<>” for non-blank cells, and “><” to select fields in data types. If this optional argument is omitted, then the method would set the criteria as All.

Operator – This is an XLAutoFilterOperator to specify the filter type, for example, xlAnd and xlOr are used to set multiple criteria options.

Criteria 2 – We can create compound filter criteria by using this 2nd argument along with Criteria 1 and Operator.

SubField – We can also apply criteria on the field from a data type.

VisibleDropDown – The AutoFilter dropdown arrow can be displayed or hidden with values True and False consecutively.

Write Code in Visual Basic Editor

To filter data based on cell value on another sheet, we need to open and write VBA code in the Visual Basic Editor. Follow the steps to open the Visual Basic Editor and write some code there.

  • Go to the Developer tab from the Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic for Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

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


1. Filter with Single Criteria Based on Cell Value on Another Sheet Using VBA in Excel

Task:
To filter the dataset (Sheet1) based on a cell value that is on another sheet (Sheet2). We want to filter the dataset for the Fruits category.

Excel VBA Filter Based on Cell Value on Another Sheet

Solution:
We need to set the arguments in the Range.AutoFilter method to use in our code.
Field 2, as the 2nd column represents the category names.
Criteria1– the cell reference of the value Fruits in Sheet2.
VisibleDropDownTrue

VBA Code:
Put the following code in the visual basic editor and press F5 to run it.

Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
    Dim category As Range
    With Worksheets("Sheet2")
        Set category = .Range("C2")
    End With
    With Worksheets("Sheet1")
        With .Range("B4:G13")
            .AutoFilter Field:=2, Criteria1:=category, VisibleDropDown:=True
        End With
    End With
End Sub

In the above code, we set the source data range as B4:G13 in Sheet1 and the cell reference for criteria1 is C2 (variable named as “category”) in Sheet2.

Output:
The dataset is filtered for the products that belong to the Fruits category.

Excel VBA Filter Based on Cell Value on Another Sheet

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


2. Run a VBA Code to Filter with Multiple Criteria Using OR Operator Based on Cell Value on Another Sheet

Task:
To filter the dataset (Sheet3) based on cell values that are on another sheet (Sheet4). We want to filter the dataset for the products that are either Apple or Tomato.

Excel VBA Filter Based on Cell Value on Another Sheet

Solution:
We need to set the arguments in the Range.AutoFilter method to use in our code.
Field 3, as the 3rd column represents the product names.
Criteria1– the cell reference of the value Apple in Sheet4.
Operator- xlOr
Criteria2- the cell reference of the value Tomato in Sheet4.
VisibleDropDownTrue

VBA Code:
Put the following code in the Visual Basic Editor and press F5 to run it.

Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
    Dim product1, product2 As Range
    With Worksheets("Sheet4")
        Set product1 = .Range("C2")
        Set product2 = .Range("E2")
    End With
    With Worksheets("Sheet3")
        With .Range("B4:G13")
            .AutoFilter Field:=3, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
        End With
    End With
End Sub

Output:
The dataset is filtered for the products that are either Apple or Tomato.

Excel VBA Filter Based on Cell Value on Another Sheet

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


3. Filter with Multiple Criteria using AND Operator Based on Cell Value on Another Sheet Using VBA in Excel

Task:
To filter the dataset (Sheet5) based on cell values that are on another sheet (Sheet6). We want to filter the dataset for the products that have quantities greater than 50 but less than 60.

Excel VBA Filter Based on Cell Value on Another Sheet

Solution:
We need to set the arguments in the Range.AutoFilter method to use in our code.
Field 4, as the 4th column represents the quantity of the products.
Criteria1– the cell reference of the quantity 50 in Sheet4.
Operator- xlAnd
Criteria2- the cell reference of the quantity 60 in Sheet4.
VisibleDropDownTrue

VBA Code:
Put the following code in the Visual Basic Editor and press F5 to run it.

Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
    Dim product1, product2 As Range
    With Worksheets("Sheet6")
        Set product1 = .Range("D2")
        Set product2 = .Range("F2")
    End With
    With Worksheets("Sheet5")
        With .Range("B4:G13")
            .AutoFilter Field:=4, Criteria1:=">" & product1, Operator:=xlAnd, Criteria2:="<" & product2
        End With
    End With
End Sub

Output:
The dataset is filtered for the products having quantities greater than 50 but less than 60.

Excel VBA Filter Based on Cell Value on Another Sheet

Read More: How to Remove Filter in Excel VBA


4. Use of Drop Down List to Filter Data Based on Cell Value in Another Sheet in Excel VBA

Task:
To filter the dataset (Sheet7) based on a cell value that is on another sheet (Sheet8). We want to filter the dataset for the categories using a Drop-Down list in another sheet.

Solution:
We need to set the arguments in the Range.AutoFilter method to use in our code.
Field 2, as the 2nd column represents the category names.
Criteria1– the cell reference of the category name in Sheet8.
VisibleDropDownTrue
Create a Drop-Down List in Excel

To make a drop-down list, just follow the simple steps below-

  • Select a cell (C2 in sheet 8) on which we’ll create the drop-down list.

  • Then go to the Data tab of the Ribbon.
  • Click on the Data Validation button.

  • In the Data Validation window, select the Setting tab (By default selected).
  • In the Allow drop-down list, choose the List option.

Excel VBA Filter Based on Cell Value on Another Sheet

  • Then type Fruits and Vegetables in the Source input box separated by a comma and finally hit OK.

Excel VBA Filter Based on Cell Value on Another Sheet

  • As an output, we can see a drop-down list in cell C2 with two options to select.

Excel VBA Filter Based on Cell Value on Another Sheet

VBA Code:
Put the following code in the Visual Basic Editor and press F5 to run it.

Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
    Dim category As Range
    With Worksheets("Sheet8")
        Set category = .Range("C2")
    End With
    With Worksheets("Sheet7")
        With .Range("B4:G13")
            .AutoFilter Field:=2, Criteria1:=category, VisibleDropDown:=True
        End With
    End With
End Sub

Output:
The dataset is filtered for the products that belong to the Fruits category.

Excel VBA Filter Based on Cell Value on Another Sheet

If we change the selection in the drop-down list to vegetables, the dataset will be filtered for the products of the category vegetables.

Excel VBA Filter Based on Cell Value on Another Sheet

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


Things to Remember

  • The AutoFilter method allows us to filter a dataset with a lot of flexibility. The xlAutoFilterOperator had different options to set a filter with multiple criteria.
  • We used the With…End With a statement to run a repetitive task in our code.

Download Practice Workbook

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


Conclusion

Now, we know how to filter data based on cell values on another sheet using Excel’s built-in functions with examples.  Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

4 Comments
  1. @ 1. Filter with Single Criteria Based on Cell Value on Another Sheet Using VBA in Excel;

    Is there a way u can filter on a word, and not the excate line of text in a cell?
    i got it working, no dropdown needed.

    • Hello Steven!
      We didn’t clearly understand your query. Can you tell us more about what you need? I assume from your comment that you may have found an alternate solution. Can you please share that with us? Thanks.

      Regards
      Md. Shamim Reza(Exceldemy Team)

  2. Is there a way to make this auto filter when the cell value is changed? Right now I need to open and run the module for the filter to work.

  3. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 23, 2023 at 11:29 AM

    Hello ABIGAYLE PAULSON
    Thank you for reporting on this fascinating issue. I have reviewed this article and found an interesting idea to solve your problem. For illustration, let’s walk through 2nd Example. Filtering the dataset (Sheet3) based on cell values on another sheet (Sheet4). The context filters the dataset for Apple or Tomato products.
    VBA Code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim product1, product2 As Range
            
        If Target.Worksheet.Name = "Sheet4" And (Target.Address = "$C$2" Or Target.Address = "$E$2") Then
            With Worksheets("Sheet4")
                Set product1 = .Range("C2")
                Set product2 = .Range("E2")
            End With
            With Worksheets("Sheet3")
                With .Range("B4:G13")
                    .AutoFilter Field:=3, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
                End With
            End With
        End If
    End Sub

    If the changed cell is either C2 or E2, the macro will execute and filter the data in “Sheet3” based on the new values in these cells. Note that the Worksheet_Change event must be placed in the code module for the “Sheet4” worksheet.
    The changes we must make to the original code to create the auto-filtering behavior:

      1) We have to add a Private Sub Worksheet_Change(ByVal Target As Range) procedure to the code module for the “Sheet4” worksheet. This procedure runs automatically whenever a cell value is changed on this worksheet.
      2) We can add an If statement to check whether the changed cell is in Sheet4 and C2 or E2. If so, the macro continues executing; if not, it exits without doing anything.
      3) We need to move the Dim statements for product1 and product2 inside the If statement, so they are only declared if the macro is going to run.
      4) The rest of the macro code is the same as the original code, so it will apply the same filter to “Sheet3” based on the new values in “Sheet4” whenever the cell value changes.

    By adding this Worksheet_Change procedure to the code module, the macro will run automatically whenever a change is made to the specified cells in “Sheet4” without manually opening and running the macro.
    Regards
    Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo