CODEWITHSUNDEEP
vbaexcelfiltercriteria
gonzalloe
gonzalloe

Reputation: 313

How to filter multiple data using keywords in VBA?

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:

Sample

Upvotes: 0

Views: 2557

Answers (2)

Rik Sportel
Rik Sportel

Reputation: 2679

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

Upvotes: 1

AiRiFiEd
AiRiFiEd

Reputation: 311

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.

Upvotes: 1

Related Questions