Auto Filter depending on Cell Value

Solved/Closed
Issa - Aug 3, 2011 at 05:56 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Nov 15, 2014 at 05:21 AM
Hello,
I have data in a table, lets say the table is (A7:M200) and the Headings in this table is (A7:M7).
Column 1 (A8:A200) is the accounts numbers.
I already selected the Row 7 (the headings in this table) then I made Auto filtering.
Now my request is when I write in Cell (E1) any account number to filter that table (A8:M200) according to this selection in E1.
Thank you in advance for any kind assistant.
Issa
Related:

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 3, 2011 at 10:11 AM
Use sheet change event to update the filter
44
Dear rizvisa1,

Thank you to your prompt reply.

I don't know what you mean by your answer "Use sheet change event to update the filter", how it works?.
I think I need VBA code to do my request? can you provide me with this code.

Thank you again for any kind assistance.

Issa
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 4, 2011 at 09:56 AM
Yes you need VBA
1. Open the file
2. Press ALT + F11
3. Press CTRL + R
4. Double click on the sheet where you want this functionality
5. Paste the code

Private Sub Worksheet_Change(ByVal Target As Range) 
   If (Intersect(Target, Range("E1")) Is Nothing) _ 
   Then 
      Exit Sub 
   End If 
   Cells.AutoFilter Field:=1, Criteria1:="=" & Range("E1") 
End Sub


This is based on assumption that you want to filter the 1st column (column A). Change value of "Field" to point to right column

By the way you can use wild card in E1 like
1234* ( some thing that starts with 1234)
123?56??? (some thing that start with 123, then has some character, followed by 56 followed by three charachters
*1234* (some thing that has 1234 some where)
0
It's solved :)

Thank you very much to all your assistance.

Issa
0
hi the suggestion was good but could u provide me after the filter I want all data to display back
0
just delete any written text in field "E1"
0
Hello

This has proved really useful but does anybody know how to now do this for multiple selections? i.e filter more than 1 column?

I'm wanting to do this so by answering a couple of questions will help find the right answer....

I could also do with a reset command to remove all filters once done. Any help will be greatly appreciated.

Thanks
Stuart
4
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Nov 15, 2014 at 05:21 AM
Stuart,
when you say "multiple selections", you mean like "OR" condition on same column or "AND" on various columns
0