Md. Abdul Kader
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.
Download Practice Workbook
4 Methods to Filter Based on Cell Value Using Excel VBA
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.
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 are involved 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 on the basis of 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.
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.
➤ Secondly, go to Insert > Module.
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
⧭ 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.
Read More: How to Add Filter in Excel (4 Methods)
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.
➤ 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.
After pressing OK, you’ll see the following 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.
➤ Within seconds, you’ll get the blank space between the starting 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
⧭ 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 must 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.
Immediately, you’ll get the following filtered data based on the Web platform.
Read More: Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)
Similar Readings
- How to Use Profit Percentage Formula in Excel (3 Examples)
- How to Copy and Paste When Filter Is Applied in Excel
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- How to Filter by Color in Excel (2 Examples)
- How to Filter Excel Pivot Table (8 Effective Ways)
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 actually not available along with the whole dataset and the Category2 is about the Sport.
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
⧭ 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 criteria 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.
Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)
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.
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
⧭ 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. Else it will return FALSE.
If you run the code, you’ll get the following output.
Read More: Filter Different Column by Multiple Criteria in Excel VBA
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.
I would like to be able to run VBA code with a keyboard shortcut that mimics right click filter on selected cells value.
Hello, JOHN!
You can run the code by pressing the keyboard shortcut F5.
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
Dear MARK,
Thank you for your query.
Here is the dataset I will use to show the solution to your problem.
After creating a PivotTable, I have copied the PivotTable 5 times. So, there are 5 PivotTables in my worksheet now.
Now, we have to create a drop-down menu from the list of PivotTables.
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.
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