How to filter multiple data using keywords in VBA?

Question

I want to filter out the unnecessary data with these 3 keywords: Proton, Hyundai, Perodua. But I am getting the "Named argument not found" error after I run this code.

''Filtering return reason for Sheet1
Rows("1:1").Select
Selection.AutoFilter

Dim rng As Range

Set rng = ActiveSheet.Range("A1:L2671")
FilterField = WorksheetFunction.Match("Car", rng.Rows(1), 0)

'Turn on filter if not already turned on
If ActiveSheet.AutoFilterMode = False Then rng.AutoFilter

'Filter Specific Countries
rng.AutoFilter Field:=FilterField, Criteria1:="=*Proton*" _
    , Operator:=xlOr, Criteria2:="=*Hyundai*" _
    , Operator:=xlOr, Criteria3:="=*Perodua*"

The Criteria3 was highlighted after I ran this code. Why can't I insert the 3 criteria onto the filter filed?

Here's the example of my data:

Possible duplicate of Use autofilter on more than 2 criteria — Robin Mackenzie
– Robin Mackenzie, Commented Jul 4, 2017 at 3:22
@RobinMackenzie this link using the array. How do I using the array together with the keywords to search? — gonzalloe
– gonzalloe, Commented Jul 4, 2017 at 3:39

AiRiFiEd · Accepted Answer · 2017-07-04 03:21:39Z

1

I am not sure if autofilter can take more than 2 criteria as given in MSDN article

However, you may want to try passing an array into criteria1 as proposed in another solution here

Personally, I would prefer to use helper columns with functions (e.g. if statements) to narrow down my selection before using autofilter.

answered Jul 4, 2017 at 3:21

AiRiFiEd

3112 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

gonzalloe Over a year ago

But if I am using array, I need to write down all type of cars from these 3 car brands. Is there any better suggestion?

AiRiFiEd Over a year ago

@Icc yea that's the problem with this solution. As I am unsure of your exact conditions, at this point, I would think something along the lines of creating a helper column =if(find("Hyundai",A1)>0, 1 , 0) and filtering the helper column to obtain the results that you want. Do let me know if this helps.

gonzalloe Over a year ago

It's really helpful but time-consuming though. Anyway, I appreciate for your kind help. :)

AiRiFiEd Over a year ago

@Icc actually you will probably have to write the formula only once and drag it down since finding Hyundai will essentially be similar to your original "Hyundai" - meaning it should capture the different models by Hyundai, you don't actually have to include every model in the formula

AiRiFiEd Over a year ago

yea sounds viable..just do note that vba speed is partly determined by the number of times the code "hits" the worksheet and using autofilter will likely be slower if u were to process the options in your code. However, if speed isnt your concern, please ignore the above. Congrats on your solution!

|

Rik Sportel · Accepted Answer · 2017-07-04 09:08:30Z

1

The problem occurs because of using Wildcards. In those cases you can not use more than 2 filter values at the same time.

This doesn't work:

rng.AutoFilter Field:=FilterField, _
    Criteria1:=Array("*Hyundai*","*Proton*","*Perodua*"), _
    Operator:=xlFilterValues

However, you could use pattern matching by first getting your range to be filtered in an array and dynamically create the values to be filtered array.

Good sample code is in this answer

answered Jul 4, 2017 at 9:08

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to filter multiple data using keywords in VBA?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related