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?
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:
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
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:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoSo 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
To post as a guest, your comment is unpublished.· 3 months agoIs there a way to have it continue to filter with additional boxes. When I write it as ElseIf, it only follows the ElseIf command.
To post as a guest, your comment is unpublished.· 4 months agoPrivate 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")
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
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 4 months agoThe VB script worked beautifully. Many thanks for the post!
To post as a guest, your comment is unpublished.· 4 months agoWhat happens if you have GRADE11 and GRADE12 for example. Will the filter show these also if you try and filter