Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to AutoFilter Rows Based on Cell Values in Excel?
Generally, in Excel, we can always apply a filter to a list just by using a slicer, and we can click on the cell value you want to filter internally. But if you want to apply the filter based on a cell that is not inside the table, we cannot do it with the help of Slicer. We can complete this process with the help of the VBA application. We can apply the filter using an external value.
This tutorial will help you understand how you can auto-filter rows based on a cell value in Excel.
Step 1
Here we will insert the VBA code for the sheet, then click Enter. Let us see a straightforward process to know how we can auto-filter rows based on a cell value in Excel. We will be using the help of the VBA application to complete our process. We will be filtering the data based on where the results fail.
To begin, right-click on the sheet name and select View Code to open the VBA application, or use the command Alt+11 to open the VBA application and type the programme in the text box as shown in the image below.
Program
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated By Nirmal
If Target.Address = Range("E2").Address Then
Range("A1:C16").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub
In the program, A1:A16 is the range on which you want to apply the filter, and E1:E2 is the range of cells you want to apply the filter based on.
Step 2
Now save the sheet as macro enabled and close the vba application using the command "Alt + Q", and when you click on Enter from the cell "E1", then the data will be filtered automatically as shown in the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how you can autofilter rows based on a cell values in Excel.
3K+ Views
