How to Filter Based on Cell Value Using Excel VBA (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

When you have a larger dataset and you want to apply the filters based on a certain value, using the VBA code might be the best option. In this article, I’ll demonstrate 4 methods to filter the entire dataset based on the cell value in Excel VBA with proper explanation.


Filter Based on Cell Value Using Excel VBA: 4 Methods

Let’s introduce today’s dataset (B4:E15 cell range) as shown in the following screenshot. Here, the Number of Visits for each website is provided along with the Name and Category of the Sites. Besides, the dates and mode of Platforms are also given. Now, you need to filter based on a specific cell value.

Dataset

Before going to the methods, I would like to say that the first two methods are mainly single criteria-based whereas the rest two methods involve multiple criteria.

Let’s dive into the methods.


1. Filter Based on a Certain Cell Value

In the beginning method, you’ll see the simple method to filter the entire dataset based on a certain value. For example, “Education”, one of the Category of the Sites, is available in the G5 cell.

That means the row number and column number of the location of the cell are 5 and 7 respectively. However, you can filter the entire dataset considering the cell value if you want.

Using the Simple Method

Let’s explore the method in a step-by-step process.

Step 01: Inserting a Module

Needless to say, you need to create a module to insert the VBA code.

➤ Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

➤ Secondly, go to Insert > Module.

How to Insert VBA Code

Step 02: Copying VBA Code

After creating the module, copy the following code.

Sub Filter_CellValue1()
ActiveSheet.Range("B4:E15").AutoFilter field:=2, Criteria1:=Cells(5, 7).Value
End Sub

Excel VBA Filter Based on Cell Value

⧭ In the above code, I used the ActiveSheet property and the Range object to assign the entire dataset. Then, I utilized the AutoFilter method along with the field as 2 and Criteria1 as the value of the G5 cell. Here, the value of the field is 2 since the Category of the Sites is located at the second position from the left of the dataset. Besides, I specified the value of the cell using the Cells property.

Step 03: Running VBA Code

When you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the following filtered dataset.

Excel VBA Filter Based on Cell ValueRead More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)


2. Filter Based on Cell Value Using a Drop-down List 

In this method, you’re going to master the filtering method including a drop-down list. Let’s say you assign a  list with a drop-down list. Later, if you choose any of the options from the drop-down list, the dataset will be filtered automatically. Follow the steps below to execute the task.

Step 01: Creating a Drop-down List

➤ Initially, choose the Data Validation tool from the Data Tools ribbon in the Data tab while selecting the cell (e.g. G5 cell) where you want to create the drop-down list.

Using a Drop-down List

➤ Shortly, you’ll see the following dialog box where you need to choose the List under the Allow criteria and insert manually All Platforms, Web, Mobile under the blank space of Source criteria. Alternatively, you may create a drop-down list with unique values if the number of unique values is not few.

Using a Drop-down List

After pressing OK, you’ll see the following drop-down list.

Using a Drop-down List

Step 02: Inserting VBA Code

➤ Now, you have to insert the VBA code. Just go to the Sheet tab of the active sheet and choose the View Code option by right-clicking.

Using a Drop-down List

➤ Within seconds, you’ll get the blank space between the start and end of the statement. And copy-paste the following code here.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$5" Then
 If Range("G5").Value = "All Platforms" Then
  Range("B5").AutoFilter
 Else
  Range("B5").AutoFilter field:=3, Criteria1:=Range("G5").Value
 End If
End If
End Sub

Excel VBA Filter Based on Cell Value Using a Drop-down List

⧭ In the above code, I used Worksheet.Change event which is suitable for dealing with the continuous changes in cell value within the worksheet. Then, I utilized the If…Then…Else statement along with the AutoFilter method to filter the dataset based on either “All Platforms” (with no criteria) or other values of the G5 cell (with criteria).

Step 03: Filtering from the Drop-down List

➤ To filter the entire dataset by clicking over the drop-down list, you just need to close the VBA Editor. Then, go to the main worksheet and choose any platform from the drop-down list. Suppose, you choose the Web platform to filter the dataset. Let’s see what is going to happen.

Excel VBA Filter Based on Cell Value Using a Drop-down List

Immediately, you’ll get the following filtered data based on the Web platform.

Excel VBA Filter Based on Cell Value Using a Drop-down List

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


3. Filter with Multiple Criteria Using OR Operator

If you want to apply multiple criteria while filtering the entire dataset, you may use this method. Particularly, this method discusses the application of the OR operator.

Assuming that you have two categories of the sites in the G5:H5 cell. Category1 is Entertainment which is not available along with the whole dataset and Category2 is about Sport.

Multiple Criteria Using Or Operator

Next, copy the following code.

Sub Using_OR_Logic()
Dim range_to_filter As Range

Set range_to_filter = Range("B4:E15")

range_to_filter.AutoFilter field:=2, Criteria1:=Cells(5, 7).Value, Operator:=xlOr, Criteria2:=Cells(5, 8).Value

End Sub

Excel VBA Filter Based on Cell Value Multiple Criteria Using Or Operator

Things to Keep in Mind While Using the Above Code:

  • Range: It refers to the cell range to filter e.g. B4:E15.
  • Field: It is the index of the column number from the leftmost part of your dataset. The value of the second field will be 2.
  • Criteria 1: The first criteria for a field e.g. Criteria1=Cells(5, 7).Value
  • Criteria 2: The second criterion for a field e.g. Criteria2=Cells(5, 8).Value
  • Operator: An Excel operator that specifies certain filtering requirements. Here, the Or operator is used which returns TRUE if any input is TRUE.

After running the code, you’ll get the filtered output based on Sport only as Category1 is not available in the dataset.

Excel VBA Filter Based on Cell Value Multiple Criteria Using Or Operator

Read More: How to Remove Filter in Excel VBA


4. Multiple Criteria Using AND Operator

The fourth and last method is to apply multiple criteria using the AND operator. If you want to filter the dataset based on specific Platforms as well as a range of the Number of Visits (between 5000 and 15000), you may use this method.

Using And Operator

Then, copy the following code into the created module.

Dim range_to_filter As Range

Set range_to_filter = Range("B4:E15")

range_to_filter.AutoFilter field:=3, Criteria1:=Cells(5, 7).Value, Operator:=xlAnd
range_to_filter.AutoFilter field:=4, Criteria1:=">=5000", Criteria2:="<=15000"

End Sub

Excel VBA Filter Based on Cell Value Multiple Criteria Using And Operator

Things to Keep in Mind While Using the Above Code:

  • Field: the value of the field for the Platforms is 3 and the Number of Visits is 5.
  • Criteria 1 of Platforms: The first criteria for a field e.g. Criteria1=Cells(5, 7).Value
  • Criteria1 and 2 of the Number of Visits: The first criteria for the field e.g. Criteria1=”>=5000”  and the second criteria is Criteria2=“<=15000” 
  • Operator: here, I used the And operator which returns TRUE if all statements are TRUE. Otherwise, it will return FALSE.

If you run the code, you’ll get the following output.

Excel VBA Filter Based on Cell Value Multiple Criteria Using And Operator

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


Conclusion

That’s the end of today’s session. This is how you may filter any dataset based on the cell value in Excel VBA. I strongly believe this article will articulate your Excel journey. Anyway, if you have any queries or recommendations, please share them in the comments section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

4 Comments
  1. I would like to be able to run VBA code with a keyboard shortcut that mimics right click filter on selected cells value.

  2. Hello, I have multiple pivot tables in a single sheet.
    So, I’d like the user to be able to select which pivot table will apply the filter, from a dropdown menu in a cell.

    Is that possible? thanks
    regards
    Mark

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Aug 21, 2023 at 2:03 PM

      Dear MARK,
      Thank you for your query.
      Here is the dataset I will use to show the solution to your problem.

      dataset

      After creating a PivotTable, I have copied the PivotTable 5 times. So, there are 5 PivotTables in my worksheet now.

      PivotTable

      Now, we have to create a drop-down menu from the list of PivotTables.

      data validation

      Next, copy this VBA code into your VBA code editor. You have to change three things in this code. These are: the cell address of where you placed the drop-down menu, the filter values, and the field name that you want to filter.

      Sub Apply_Filter_PivotTable()
          Dim pivotTableName As String
          Dim pivotTable As pivotTable
          Dim field As PivotField
          Dim filterValue As String
          
          ' Change the cell address to where you placed the dropdown menu
          pivotTableName = Range("A2").Value
          filterValue = "Cash" ' Change this to the desired filter value
          
          On Error Resume Next
          Set pivotTable = ActiveSheet.PivotTables(pivotTableName)
          On Error GoTo 0
          
          If Not pivotTable Is Nothing Then
              ' Change "Field Name" to the name of the field you want to filter
              Set field = pivotTable.PivotFields("Payment Method")
              
              field.ClearAllFilters
              field.CurrentPage = filterValue
          Else
              MsgBox "Pivot table not found."
          End If
      End Sub
      
      Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address = "$A$2" Then ' Change to the address of your dropdown cell
              ApplyPivotTableFilter
          End If

      VBA code

      To get the output, select the PivotTable from the drop-down which you want to filter, and then Run the code by pressing the F5 key.
      For your convenience, I have given the Excel file: Filtering PivotTable with drop-down menu.xlsm

      Regards
      Mahfuza Anika Era
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo