How to Filter a Dataset Based on a Cell Value from Another Sheet Using VBA (4 Methods)

Let’s introduce our dataset first. 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 utilize the Range.AutoFilter method in VBA to filter a dataset using the AutoFilter feature.

This method accepts several arguments for customization:

Field: An integer offset representing the field (column) to set the filter on. The count starts from the leftmost side of the dataset (1-based index).
Criteria1: The filter criteria. For example, use “=” for blank cells, “<>” for non-blank cells, and “><” for specific data types. If omitted, the method sets the criteria to include all values.
Operator: An XLAutoFilterOperator to specify filter types (e.g., xlAnd and xlOr for multiple criteria).
Criteria2: Allows creating compound filter criteria when used alongside Criteria1 and Operator.
SubField: Apply criteria to fields within a data type.
VisibleDropDown: Determines whether the AutoFilter dropdown arrow is displayed (True) or hidden (False).

Write Code in Visual Basic Editor

To filter data based on a cell value from another sheet, follow these steps:

Go to the Developer tab in the Ribbon.
Click the Visual Basic option.

In the Visual Basic for Applications window, select Insert and choose New Module.

Write your code inside the Visual Code Editor and press F5 to run it.

Method 1 – Filter with Single Criteria Based on Cell Value from Another Sheet

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

Solution:

Set the arguments for the Range.AutoFilter method:
- Field: 2 (since the 2nd column represents category names).
- Criteria1: The cell reference for the value “Fruits” in Sheet2.
- VisibleDropDown: True.

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 provided code, the source data range is B4:G13 in Sheet1, and the cell reference for Criteria1 is C2 (stored in the variable “category”) in Sheet2.

Output:
The dataset is filtered to display products that belonging to the Fruits category.

Method 2 – Filtering with Multiple Criteria Using OR Operator Based on Cell Value from Another Sheet

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

Solution:

Set the arguments for the Range.AutoFilter method:
- Field: 3 (since the 3rd column represents product names).
- Criteria1: The cell reference of the value “Apple” in Sheet4.
- Operator: xlOr.
- Criteria2: The cell reference of the value Tomato in Sheet4.
- VisibleDropDown: True.

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 products that are either Apple or Tomato.

Method 3 – Filtering with Multiple Criteria Using AND Operator Based on Cell Value from Another Sheet

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

Solution:
Set the arguments for the Range.AutoFilter method:

Field: 4 (since the 4th column represents product quantities).
Criteria1: The cell reference of the quantity 50 in Sheet4.
Operator: xlAnd.
Criteria2: The cell reference of the quantity 60 in Sheet4.
VisibleDropDown: True.

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.
VisibleDropDown– True

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 with quantities greater than 50 but less than 60.

Read More: How to Remove Filter in Excel VBA

Method 4 – Using a Drop-Down List to Filter Data Based on Cell Value from Another Sheet in Excel VBA

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

Solution:

Set the arguments for the Range.AutoFilter method:
- Field: 2 (since the 2nd column represents category names).
- Criteria1: The cell reference of the category name in Sheet8.
- VisibleDropDown: True.

Creating a Drop-Down List in Excel:

Select cell C2 in Sheet8.

Go to the Data tab on the Ribbon.
Click the Data Validation button.

In the Data Validation window, select the Setting tab.
Choose List from the Allow drop-down list.

Type Fruits, Vegetables in the Source input box (separated by a comma) and click OK.

Now cell C2 will display a drop-down list with two options to select.

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 products belonging to the Fruits category.

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

Things to Remember

The AutoFilter method provides flexibility for filtering datasets.
The xlAutoFilterOperator offers various options for setting filters with multiple criteria.
We utilized the With…End With statement to execute repetitive tasks in our code.

Download Practice Workbook

You can download the practice workbook from here:

Filter Based on Cell Value on Another Sheet.xlsm

Get FREE Advanced Excel Exercises with Solutions!

6 Comments

Steven Jul 19, 2022 at 7:43 PM

@ 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.
- Reply
  Md. Shamim Reza Aug 30, 2022 at 11:06 AM
  
  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)
Abigayle Paulson Mar 21, 2023 at 8:21 PM

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.
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:
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
ODMA Feb 16, 2024 at 10:00 PM

Like the previous question mentioned, the code only works when you manually execute the module if you change the value. But is there a way to make it automatic?
Specifically for the 4th example (drop-down list) and if there is a way to add a condition: if the drop-down list is blank, do not filter.
- Reply
  Lutfor Rahman Shimanto Feb 22, 2024 at 4:29 PM
  Hello ODMA
  
  Thanks for reaching out and sharing your queries. The previous question by ABIGAYLE PAULSON was to automate the idea that the filtering algorithm will be applied if the drop-down list in Sheet8 is changed. After analyzing your requirements, I understand that you do not want to use the filter algorithm if the drop-down is empty.
  
  I have developed an Excel VBA Worksheet_Change Event that will fulfil your goal. All you need is to paste the following code in the sheet module of Sheet8 (the sheet that contains the drop-down).
  
  Excel VBA Event Procedure:
```
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim category As Range
    Dim dropdownCell As Range
    Dim ws7 As Worksheet
    Dim ws8 As Worksheet
    
    Set ws7 = Worksheets("Sheet7")
    Set ws8 = Worksheets("Sheet8")
    
    Set dropdownCell = ws8.Range("C2")
    
    If Not Intersect(Target, dropdownCell) Is Nothing Then

        If dropdownCell.Value = "" Then
            ws7.AutoFilterMode = False
        Else
            Set category = dropdownCell
            With ws7.Range("B4:G13")
                .AutoFilter Field:=2, Criteria1:=category.Value
            End With
        End If
    End If

End Sub
```
  OUTPUT Overview:
  
  Hopefully, the event procedure will help; good luck.
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy