#1
|
|||
|
|||
Multiple Filter HELP
Hi
I need help to use Multiple Filter using ARRAY. I explained the my problem in attached Excel sheet. I need to learn using together AND or CONTAIN operators. Thanks in advance. |
#2
|
||||
|
||||
I couldn't find a way of doing this with an array of more than 2 elements while using AND logic, so a convoluted way:
Code:
Sub blah() crits = Array("BYCYCLE", "MOTOR", "12V") 'Is there an autofilter on the sheet already?: On Error Resume Next Set myAutoF = ActiveSheet.AutoFilter On Error GoTo 0 If myAutoF Is Nothing Then Range("A1:B1").AutoFilter 'if there isn't, add one else use existing one. This behaviour can be tweaked. 'get the range of column 2 databody: Set myrng = ActiveSheet.AutoFilter.Range.Columns(2) '2nd column Set myrng = Intersect(myrng, myrng.Offset(1)) 'data body only Set mydic = CreateObject("scripting.dictionary") 'dictionary to hold full strings of those conforming to criteria. For Each cll In myrng.Cells 'go through each cell (if range is huge we can speed this up by doing it in-memory) found = True For Each sstr In crits If Not cll.Value Like "*" & sstr & "*" Then 'if any string NOT found then stop the loop and move to next cell found = False Exit For End If Next sstr If found Then mydic(cll.Value) = vbNullString 'if it's passed all the tests then add full cell content to dictionary Next cll 'If at least one found then filter for it else don't filter: If mydic.Count > 0 Then ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=mydic.keys, Operator:=7 End Sub |
#3
|
|||
|
|||
It is working wonderfull. It is awsome!
It works with three words. Is it possible to increase words more than three ? For example four, five and six? I don't know if I want the impossible. Thank you very much for your support. |
#4
|
||||
|
||||
I suggest you try it. The answer should be Yes.
|
#5
|
|||
|
|||
Thank you !
I have last question; I will use this code for 8 column (A1:H1) And I will filter the table according to column 5. Which correction will be done? Is it enough to correct as follows: Set myrng = ActiveSheet.AutoFilter.Range.Columns(2) -> Columns(5) If mydic.Count > 0 Then ActiveSheet.AutoFilter.Range.AutoFilter Field:=2 -> Field:=5 If myAutoF Is Nothing Then Range("A1:B1").AutoFilter -> Range("A1:H1") I made this change, but code did not run. |
#6
|
||||
|
||||
They seeem right but you need also to change the last line:
If mydic.Count > 0 Then ActiveSheet.AutoFilter.Range.AutoFilter Field:=5, Criteria1:=mydic.keys, Operator:=7 If still it doesn't work, try manually removing the autofilter before running the macro. You should only need to do this once (if it finds an autofilter in place it leaves it). |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filter by multiple categorize? | SoMany | Excel | 0 | 05-29-2021 08:55 AM |
Filter multiple columns | ConfuddledOne | Excel | 2 | 03-26-2019 12:41 AM |
Multiple Filter? | abc3132 | Excel | 2 | 12-19-2017 11:10 AM |
Filter by multiple columns | ConfuddledOne | Excel Programming | 0 | 01-30-2017 08:00 PM |
Filter tasks with multiple criteria | markhad | Outlook | 0 | 03-15-2016 02:48 AM |