Need Autofilter for more than 2 criteria - Array is not working

[ragav_in]

Dear All,

I am trying to filter a range in excel (15 columns) and the filter criteria to be applied on a column (Column 2) which contains description. I want to apply filter on that column to get only the rows of data that meet 3 criteria such as "PMP", "PM Plan", "Project Management Plan". If I use Criteria to filter, I can filter only on 2 values (Criteria1:="*PMP*") & Criteria2:="=*PM Plan*". If I need to search on 3rd criteria, I thought of using Array where I define the Array as Variant and provide these 3 values, and then filter on Array. I am providing the snippets below for your reference.

For 2 Criteria: - This works
ActiveSheet.Range("$A$1:$N$300").AutoFilter Field:=2, Criteria1:="=*PMP*" _
, Operator:=xlOr, Criteria2:="=*PM Plan*"

For more than 2, I use Array: This does not work

Dim arr1 as Variant
arr1 = Array("PMP", "PM Plan", "Project Management Plan")
ActiveSheet.Range("$A$1:$N$300").AutoFilter Field:=2, Criteria1:=arr1, Operator:=xlFilterValues

Please let me know where am I missing.
Also let me know if you need more information, I can provide. I thank you all in advance for your help on this.

Thanks,
ragav_in

 

[ragav_in]

Hi Akuini,

Thanks much for your assistance, your code is of great help to me and I will use it. I have a request here, as mentioned in my above reply to Peter, I need to exclude the rows that contain the word "PMPR" when it filters for the text PMP, PM Plan and Project Management Plan. Your code filters on these 3 critieria, but the rows with values PMPR is also included, and I need to exclude them as they form another set. Is there a way to exclude rows containing the text "PMPR" while performing the above operation?

Thanks much for your support and in advance for the time you have spent to help me resolve this. I am grateful to you.

Thanks,
ragav_in

Here's another way.
Using col P as helper column (its content is deleted at the end).

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1110786b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1110786-need-autofilter-more-than-2-criteria-array-not-working.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] ary
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
n = [COLOR=crimson]300[/COLOR]

[I][COLOR=seagreen]'col P as helper column[/COLOR][/I]
[COLOR=Royalblue]Set[/COLOR] c = Range([COLOR=brown]"P1:P"[/COLOR] & n): c.Value = [COLOR=brown]""[/COLOR]

ary = Split([COLOR=brown]"PMP|PM Plan|Project Management Plan"[/COLOR], [COLOR=brown]"|"[/COLOR])
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
ActiveSheet.AutoFilterMode = [COLOR=Royalblue]False[/COLOR]

[COLOR=Royalblue]With[/COLOR] Range([COLOR=brown]"A1:A"[/COLOR] & n)
    [COLOR=Royalblue]For[/COLOR] i = LBound(ary) [COLOR=Royalblue]To[/COLOR] UBound(ary)
        .AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=[COLOR=brown]"*"[/COLOR] & ary(i) & [COLOR=brown]"*"[/COLOR]
        [COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]Resume[/COLOR] [COLOR=Royalblue]Next[/COLOR]
        c.SpecialCells(xlCellTypeVisible).Value = [COLOR=brown]"y"[/COLOR]
        [COLOR=Royalblue]On[/COLOR] [COLOR=Royalblue]Error[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] [COLOR=crimson]0[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
    .AutoFilter
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

c.AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=[COLOR=brown]"y"[/COLOR]
c.Value = [COLOR=brown]""[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Akuini]

There are some values in this column that contain "PMPR", which I don't need. When I run this code, since "PMP" is being considered, "PMPR" is also included in the filter, which is not required.

Do you mean the criteria is "PMPR" or "*PMPR*"? Just change the criteria to suit in this part:
.AutoFilter Field:=1, Criteria1:="*PMPR*"

[FONT=lucida console][color=Royalblue]Sub[/color] a1110786b()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1110786-need-autofilter-more-than-2-criteria-array-not-working.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], n [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] ary
[color=Royalblue]Dim[/color] c [color=Royalblue]As[/color] Range
n = [color=crimson]300[/color]

[i][color=seagreen]'col P as helper column[/color][/i]
[color=Royalblue]Set[/color] c = Range([color=brown]"P1:P"[/color] & n): c.Value = [color=brown]""[/color]

ary = Split([color=brown]"PMP|PM Plan|Project Management Plan"[/color], [color=brown]"|"[/color])
Application.ScreenUpdating = [color=Royalblue]False[/color]
ActiveSheet.AutoFilterMode = [color=Royalblue]False[/color]

[color=Royalblue]With[/color] Range([color=brown]"A1:A"[/color] & n)
    [color=Royalblue]For[/color] i = LBound(ary) [color=Royalblue]To[/color] UBound(ary)
        .AutoFilter Field:=[color=crimson]1[/color], Criteria1:=[color=brown]"*"[/color] & ary(i) & [color=brown]"*"[/color]
        [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]Resume[/color] [color=Royalblue]Next[/color]
        c.SpecialCells(xlCellTypeVisible).Value = [color=brown]"y"[/color]
        [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]GoTo[/color] [color=crimson]0[/color]
    [color=Royalblue]Next[/color]
        
        .AutoFilter Field:=[color=crimson]1[/color], Criteria1:=[color=brown]"*PMPR*"[/color]
        [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]Resume[/color] [color=Royalblue]Next[/color]
        c.SpecialCells(xlCellTypeVisible).Value = [color=brown]""[/color]
        [color=Royalblue]On[/color] [color=Royalblue]Error[/color] [color=Royalblue]GoTo[/color] [color=crimson]0[/color]
    
    .AutoFilter
[color=Royalblue]End[/color] [color=Royalblue]With[/color]

c.AutoFilter Field:=[color=crimson]1[/color], Criteria1:=[color=brown]"y"[/color]
c.Value = [color=brown]""[/color]

Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

 

[Peter_SSs]

However, I have another challenge. As I am searching for PMP, PM Plan, Project Management Plan in the column and filtering them, I stumbled on this issue. There are some values in this column that contain "PMPR", which I don't need. When I run this code, since "PMP" is being considered, "PMPR" is also included in the filter, which is not required.

I would like to know from you, if there is any way to exclude the rows that contain the text "PMPR", but still filter the records that contain "PMP". It might look like am a bit greedy here, but still since I could not find an option to exclude "PMPR" in the code you have given, I am requesting this to you.

No, that is an easy change to my AutoFilter code. Just add in this blue code to the relevant line

If InStr(1, itm, vals(i), 1) And Not InStr(1, itm, "PMPR", 1) Then

Note again that, if you already have AutoFilter applied to your A:N column range, this code will preserve that AutoFilter enabling you to manually add/delete/amend filtering criteria, whereas akuini's code does not preserve that.
If you do (or might) happen to have an AutoFilter applied to some other range in the sheet (ie not A:N) then you would need to add this line near the top of my code

ActiveSheet.AutoFilterMode = False