How to autofilter rows based on cell value in Excel?

Normally, the Filter function in Excel can help us to filter any data as we need, but, sometimes, I would like to auto filter cells based on a manual cell input which means when I enter a criteria in a cell, the data can be filtered automatically at once. Are there any good ideas to deal with this job in Excel?

Auto filter rows based on cell value you entered with VBA code

Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive

Auto filter rows based on cell value you entered with VBA code

Supposing, I have the following range of data, now, when I enter the criteria in cell E1 and E2, I want the data will be filtered automatically as below screenshot shown:

doc auto filter 1

1. Go the worksheet that you want to auto filter the date based on cell value you entered.

2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:

VBA code: auto filter data according to entered cell value:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
   If Target.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

doc auto filter 2

Note: In the above code, A1:C20 is your data range that you want to filter, E2 is the target value that you want to filter based on, and E1:E2 is your criteria cell will be filtered based on. You can change them to your need.

3. Now, when you entering the criteria in cell E1 and E2 and press Enter key, your data will be filtered by the cell values automatically.

Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive

Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive, etc.

Kutools for Excel’s Super Filter feature is a powerful utility, you can apply this feature to finish the following operations:

Filter data with multiple criteria; Filter data by text length;
Filter data by upper / lower case; Filter date by year / month / Day / week / quarter

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Demo: Auto filter rows based on cell value you entered with VBA code

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

36 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Ofer Hirshfeld · 1 months ago
hi

thank you - this code was a life saver

but if I wish to filter when the value on E2 contains the search text.
so if i search for Grade1
it should filter
Grade1
ExampleGrade1
Grade1ETC

many thanks in advance
- Reply
To post as a guest, your comment is unpublished.
Aaron · 9 months ago
I scanned through the comments and didn't see a specific answer. I am looking to filter a Table that changes based on a DB query, The table range will change every time the query is refreshed, How can we account for this in the code? The function becomes pointless if I have to direct users edit the VBA code every time they refresh the table.
- Reply
To post as a guest, your comment is unpublished.
Henk · 9 months ago
Good day,

I have made sheet1 with the table in the example. In sheet2 all the data are referenced to the table in sheet1.

In sheet2 I can choose the grades exactly as in the example. That works OK.

However when I change a Grade of a student in sheet1 the list is not updated in sheet2. So the filter does not work automatic.

How can this be done?
- Reply
To post as a guest, your comment is unpublished.
minnie · 9 months ago
Hello, is there a way to quickly modify the VBA code to filter on values bigger than in selected cell?
- Reply
To post as a guest, your comment is unpublished.
Stan · 11 months ago
Hi there,

Thank you for the great content, however I am having some issues and was wondering whether you had any ideas why.

I have adjusted my ranges so my code reads:

Sub AutoFilter()
If Target.Address = Range("E13:F14").Address Then
Worksheets('Data Archive').Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("F10:F1048576")
End If
End Sub

However, when I run the macro a "Compile Error: Syntex Error" message appears.

Thank you in advance

Stan
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 10 months ago
  
  Hello, Stan,
  Do you set the data range and criteria range correctly in the code？Or you can take your data range as a screenshot here.
  Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Vincent VBA · 1 years ago
Thank you very much for this. It worked for me fine to a certain extent, please let me explain:

I have multiple tables in my workbook. When I applied the above code, it only worked for one table, but not for the other two tables. Let's say the first table is, as in your example, from A1:C20. The second table is from A22:C40. The third from A42:C60. All tables have the info about the "Grade", however they have different columns & Date and therefore cannot be consolidated into one big tables but rather 3 smaller tables.

How would the code need to like like so that if I put the criteria: "Grade1", all 3 tables will be automatically filtered for "Grade1" ?

Thank you very much in advance.

Best regards
- Reply
To post as a guest, your comment is unpublished.
Peter Johansen · 1 years ago
Thank you, for this, it was really helpful. i just have one question, after selected the name in the drop down menu (i have names as criteria), it only shows people with that name, as it should, but how can i do so after i have selected a name, then i want so see all the rows of my table??

hope you can help me out.

kind regards
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, Peter,
  This code is worked well for fitering the entire rows of the data, could you enter your table range correctlly?
  
  A1:C20 is the range of your data, E1:E2 is the criteria range.
  Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
  
  doc-auto-filter.png
  
  Reply
To post as a guest, your comment is unpublished.
tim · 1 years ago
Hey guys,
perfect Explanation, thank you very much.
1 Little question: if I want to filter with 2,3 4 or more criterias how do I do this?
For example I want to say I wanna see the Name Henry, with Grade 1 and this Age...so not just 1 criteria but for example 3..=?

thanks for the respond

Kind regards,

TIM
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, tim,
  To auto filter data based on multiple criteria, you should apply the below code: (please change the cell references to your need)
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  'Update by Extendoffice
  Dim xVStr As String
  Dim xFStr As String
  xVStr = "E22:G22" 'the criteria that you want to filter based on
  xFStr = "E21:G22" 'the range contains the header of the criteria
  If Not (Intersect(Range(xVStr), Target) Is Nothing) Then
  Range("A1:C17").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
  CriteriaRange:=Range(xFStr)
  End If
  End Sub
  
  Please try, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
Bogdan · 1 years ago
Hello,

What if I got the filtered data in a different tab(sheet 2) in the same workbook and the cell that the filter needs to refer to is in the first tab(sheet 1). I used this VBA but is not working like that, only if I have both the criteria cell(E2 in this VBA) in the same tab with the filtered data(A1:C20)
- Reply
To post as a guest, your comment is unpublished.
mjr_awesome · 1 years ago
There might be a mistake in the instructions. Instead of pasting the code into a blank Module, one should paste it into the Sheet window. For example, if the macro is to work on Sheet1, the code should be pasted into Microsoft Excel Objects -> Sheet1(Sheet1). Only then it works for me on Excel 2016.

Thanks for the code!
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, mjr,
  There is no mistake in this article, the article said, you should put the VBA code into the sheet module by right click the sheet name and then choose View Code to go to the module.
  But, your operation is correct as well.
  Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
rphil123 · 1 years ago
So I have a bunch of values and then a table of data. I am wondering if I can filter that table based on the values similarly to what is explained above. For example I would like to click on a cell that has the value of 3, which corresponds to 3 records(200 rows, 25 columns) that meet a condition and then have my table filtered to just show those records. An example of a condition would be, if one variable is great than 100. I have over 100 of these conditions which is why I would like my table to be linked to it in some way. Any help would be much appreciated. In your example provided, it would be similar to if you just wanted all ages over 3, 6, 9, 12 etc and then you had 25 similar variables.So to filter the table to show only records with age over 3 based on clicking a value from a list that says something like age>3 - 2 records, age>6 - 4 records etc
- Reply
To post as a guest, your comment is unpublished.
Elliott · 2 years ago
Is there a way to have it continue to filter with additional boxes. When I write it as ElseIf, it only follows the ElseIf command.
- Reply
To post as a guest, your comment is unpublished.
mrtyzc61 · 2 years ago
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
If Target.Address = Range("E2").Address Then
Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub

E2 HUCRESI YERINE E SUTUNUNUNA YAZILAN SON SATIRA GORE FILITRELEME YAPABILIR MI

According the code mentioned above , is it possible to make filtration according the written data to the last row of column E ?

I hope to get help and thanks for your help
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hi, murat,
  The above code works well in the whole worksheet, you just need to change the cell references to your need. Please try it, thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  mrtyzc61 · 2 years ago
  
  I don't know how can I change the cell reference.
  
  Reply
To post as a guest, your comment is unpublished.
Kent · 2 years ago
The VB script worked beautifully. Many thanks for the post!
- Reply
To post as a guest, your comment is unpublished.
Bob · 2 years ago
What happens if you have GRADE11 and GRADE12 for example. Will the filter show these also if you try and filter
on GRADE1?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Bob,
  Yes, as you said, when entering part of the text you want to filter, all the cells contain the part text will be filtered out. So, if you type Grade1, all cells contain Grade1, Grade11, Grage123...will be filtered out.
  
  Reply
To post as a guest, your comment is unpublished.
Dispatcher7 · 2 years ago
Thank you for this code. I have been trying to modify it to work better for me, but having difficulty.

My sheet has data from A2:G2280 Column A contains street names. I want to be able to type at least part of the street name into A1 and display only data that contains A1 in all or part. So if I type Bro in A1 I would see the rows that have Broad, Broadway and Brook. Of course if A1 is blank I would see everything.

Sorry I'm not fluent in the Excel VBA lingo, I'm just a 911 dispatcher that knows their is an easier way.

Thank you.

Mark
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Mark,
  To solve your problem, please apply the following VBA code:
  Note: In the below code, the A1 is the cell that you want to enter the criteria, A2:D20 is the data range, A is the column contains the criteria that you want to filter from, please change the cell references to your own.
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim xRg As Range
  Dim xRRg As Range
  Dim xFNum As Integer
  On Error Resume Next
  If Target.Address <> Range("A1").Address Then Exit Sub
  Set xRg = Range("A2:D20").CurrentRegion
  Application.ScreenUpdating = False
  If Target.Text = "" Then
  xRg.Rows.Select
  Selection.EntireRow.Hidden = False
  Application.ScreenUpdating = True
  Exit Sub
  End If
  For xFNum = 1 To xRg.Rows.Count
  Set xRRg = xRg.Range("A" & xFNum)
  xRRg.Rows.Select
  If InStr(xRRg.Text, Target.Text) > 0 Then
  Selection.EntireRow.Hidden = False
  Else
  Selection.EntireRow.Hidden = True
  End If
  Next xFNum
  Application.ScreenUpdating = True
  End Sub
  
  Please try it, hope it can help you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Carsten · 1 years ago
  
  Hi skyyang,
  your code works perfectly, but I have a question: If I don't want to have a filter criterion in cell A1 and leave the cell empty, it will no longer be displayed any line. So everything is filtered out. Just like Mark wanted "Of course if A1 is blank I would see everything" it doesn't work, does it?
  Would be nice if you could help me.
  Greetings
  Carsten
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  skyyang · 1 years ago
  
  Hi, Carsten,
  When leaving the cell A1 empty, all the data rows are displayed, when entering any character you want to filter, it will work correctly. How is your problem, please insert your problem sheet as screenshot here.
  
  Thank you!
  
  doc-filter-1.png
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Carsten · 1 years ago
  
  Hi skyyang,
  your code works perfectly, but I have a question: If I don't want to have a filter criterion in cell A1 and leave the cell empty, it will no longer be displayed any line. So everything is filtered out. Just like Mark wanted "Of course if A1 is blank I would see everything" it doesn't work, does it?
  Would be nice if you could help me.
  Greetings
  Carsten
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Dispatcher7 · 2 years ago
  
  Thanks for the help.
  I changed A2:D20 to A3:G2281 to represent my data field. Now when I type anything in cell A1 and tab out of the cell rows 2-109 are hidden. It is not filtering and displaying only rows that contain all or in part what is entered in cell A1.
  
  Any ideas?
  
  Reply
To post as a guest, your comment is unpublished.
shahbaaz · 2 years ago
its working and awsome...thanks
- Reply
To post as a guest, your comment is unpublished.
George · 2 years ago
Thank you for this write up! I am trying to adjust the code to allow a range of acceptance.

Example: I input 5 and it filters and only shows everything that is within .5 of 5, (so 4.5 to 5.5)
- Reply
To post as a guest, your comment is unpublished.
Javier · 3 years ago
Doesn't work for me, might be that I have office 2010? doesn't do anything :S
- Reply
To post as a guest, your comment is unpublished.
Amanda · 3 years ago
Hi,

The code below works perfectly. However, how do I disable the macro if I want to unfilter?
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
If Target.Address = Range("E2").Address Then
Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  KST · 2 years ago
  
  In Range("E2").Address delete any input. All will "unfilter."
  
  Reply
To post as a guest, your comment is unpublished.
ZZted · 4 years ago
How do I undo it?it hides all of my data.
- Reply
To post as a guest, your comment is unpublished.
jdvdp · 4 years ago
I've been trying to filter a worksheet with a variety of codes (taken from various sites, including this one), but none seem to work. In a sheet with information in the cell range A101:EF999 (yes, big one), I want to autofilter the sheet based on a three letter code that I enter into cell B5, which should correspond to rows having that same code in column B101-B999. A sample snippet would look like this:

A B C D E
5 ABC
...
101 ABC
102 DEF
103 GHI
104 ABC
105 JKL
106 ABC
107 DEF

On selecting "ABC" in cell B5, only rows 101, 104 and 106 should be displayed, but nothing happens. Is there something I'm overlooking here? Any help would be much appreciated!
- Reply
To post as a guest, your comment is unpublished.
Jon · 4 years ago
THANK YOU SO MUCH FOR THE ABOVE FORMULA - IT WORKS GREAT.
- Reply

Login to your account

Password Reset