Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2022, 06:34 AM
Ongbey Ongbey is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2013
Novice
Multiple Filter HELP
 
Join Date: Jul 2022
Posts: 3
Ongbey is on a distinguished road
Default 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.
Attached Files
File Type: xlsm Multiple Filter Problem.xlsm (13.9 KB, 7 views)
Reply With Quote
  #2  
Old 07-14-2022, 04:21 PM
p45cal's Avatar
p45cal p45cal is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-15-2022, 02:09 AM
Ongbey Ongbey is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2013
Novice
Multiple Filter HELP
 
Join Date: Jul 2022
Posts: 3
Ongbey is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 07-15-2022, 02:41 AM
p45cal's Avatar
p45cal p45cal is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I suggest you try it. The answer should be Yes.
Reply With Quote
  #5  
Old 07-15-2022, 02:58 AM
Ongbey Ongbey is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2013
Novice
Multiple Filter HELP
 
Join Date: Jul 2022
Posts: 3
Ongbey is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 07-15-2022, 03:05 AM
p45cal's Avatar
p45cal p45cal is offline Multiple Filter HELP Windows 10 Multiple Filter HELP Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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).
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Filter HELP 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft