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
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
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, 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 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now! |