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

 

[Trebor76]

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

It works for me

Your code uses "Activesheet" so make sure you're on the tab in question before running. The other possibility is that the tab is protected.

Robert

 

[ragav_in]

Hi Robert,

Thanks for your response. However it does not work for me. It works only if the column contains "PMP", "PM Plan", "Project Management Plan" as whole words. If there is a cell that contains "A PMP" or "23 Project Management Plan", it is not selected. Sorry if I forgot to mention in my previous thread.

My requirement is to search in that column for all rows that contain the text "PMP", "PM Plan", "Project Management Plan" anywhere in it and not only whole words. Please help me how to achieve this.

Thanks again for your time and patience.

ragav_in

 

[Peter_SSs]

You will need to use Advanced Filter or have your code build a list of cell values that meet the criteria and then apply that list to your AutoFilter.
Post back with one of those preferences if you then still need specific help to build the code.

 

[Trebor76]

Hi Peter,

I'm curious. I've written the following code to set a range that meets the user's requirement but don't know how to filter using it as the source - can you show me how:

Option Explicit
Sub Macro1()

    'https://www.mrexcel.com/forum/excel-questions/1110421-solution-not-needed-just-clarity-piece-code.html
    
    Dim lngLastRow As Long
    Dim varMyFilterItem As Variant
    Dim rngFiltered As Range
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        .AutoFilterMode = False 'Remove all filters
        lngLastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        With .Range("B1:B" & lngLastRow)
            For Each varMyFilterItem In Array("PMP", "PM Plan", "Project Management Plan")
                .AutoFilter Field:=1, Criteria1:="=*" & CStr(varMyFilterItem) & "*"
                If rngFiltered Is Nothing Then
                    Set rngFiltered = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
                Else
                    Set rngFiltered = Union(rngFiltered, .Offset(1, 0).SpecialCells(xlCellTypeVisible))
                End If
                .AutoFilter
            Next varMyFilterItem
        End With
    End With
    
    'If 'rngFiltered' has been set, then...
    If Not rngFiltered Is Nothing Then
        '..code here to apply 'rngFiltered' to a filter
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

Hi Peter,

I'm curious. I've written the following code to set a range that meets the user's requirement but don't know how to filter using it as the source - can you show me how:

As far as I know there isn't a simple way to get from there to the filter. I think that you would then need to cycle through each cell in rngFiltered and add its value to an array to be used in the AutoFilter criteria, or build & split a string as I have done in my last code below. However, if doing that it would be simpler and much faster to do it the way I have right from the start rather than doing the individual AutoFilters and building a range.

@ragav_in

A couple of options below.

1. AdvancedFilter is the shortest code, but not necessarily the fastest (depends partly on data size) and is not as simple to clear. To clear, you need to go to Data ribbon tab & click 'Clear' in the 'Sort & Filter' group. Or else you could use the 'Clear_AdvancedFilter' code below. AdvancedFilter also removes the ability to AotoFilter other columns in the range at the same time

2. AutoFilter code is longer but ..
- maintains your AutoFilter on the range which you may want to use further on other columns
- is easy to clear using the AutoFilter drop-down at the top of the column.

Sub Using_AdvancedFilter()
  Range("Z2").Formula = "=COUNT(SEARCH({""PMP"",""PM Plan"",""Project Management Plan""},B2))" '<- Edit as required (note double quotes)
  Range("$A$1:$N$300").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Z1:Z2"), Unique:=False
  Range("Z2").ClearContents
End Sub


Sub Clear_AdvancedFilter()
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub


Sub Using_AutoFilter()
  Dim a As Variant, vals As Variant, itm As Variant
  Dim s As String
  Dim i As Long
  
  vals = Split("PMP|PM Plan|Project Management Plan", "|")  '<- Edit as required
  With Range("$A$1:$N$300")
    a = .Columns(2).Value
    For Each itm In a
      For i = 0 To UBound(vals)
        If InStr(1, itm, vals(i), 1) Then
          s = s & "|" & itm
          Exit For
        End If
      Next i
    Next itm
    .AutoFilter Field:=2, Criteria1:=Split(Mid(s, 2), "|"), Operator:=xlFilterValues
  End With
End Sub

 

[Trebor76]

Thanks Peter. I suppose another option for my code would be to paste rngFiltered on another tab.

Can you build and append to a custom view?

How's the drought in Macksville?

 

[Peter_SSs]

Thanks Peter. I suppose another option for my code would be to paste rngFiltered on another tab.

Possible problem with that (or just hiding all non rngFiltered rows) is that OP might want to filter other columns &/or unfilter some of the values the code has displayed.

How's the drought in Macksville?

We have had some rain recently but could do with more.

Can you build and append to a custom view?

Not a great strength of mine but in any case probably should start your own thread if you want to pursue that.

 

[Akuini]

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]

 

[ragav_in]

Hi Peter,

Thanks much for your code, the options you have given, and also their highlights and lowlights. This code is of very useful to me and thanks a lot for taking some time to assist me in this. Really thankful to your noble deed.

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.

Once again thanks much for the support you have extended for which I am grateful to you. Your rock

Thanks
ragav_in

As far as I know there isn't a simple way to get from there to the filter. I think that you would then need to cycle through each cell in rngFiltered and add its value to an array to be used in the AutoFilter criteria, or build & split a string as I have done in my last code below. However, if doing that it would be simpler and much faster to do it the way I have right from the start rather than doing the individual AutoFilters and building a range.


@ragav_in

A couple of options below.

1. AdvancedFilter is the shortest code, but not necessarily the fastest (depends partly on data size) and is not as simple to clear. To clear, you need to go to Data ribbon tab & click 'Clear' in the 'Sort & Filter' group. Or else you could use the 'Clear_AdvancedFilter' code below. AdvancedFilter also removes the ability to AotoFilter other columns in the range at the same time

2. AutoFilter code is longer but ..
- maintains your AutoFilter on the range which you may want to use further on other columns
- is easy to clear using the AutoFilter drop-down at the top of the column.

Sub Using_AdvancedFilter()
  Range("Z2").Formula = "=COUNT(SEARCH({""PMP"",""PM Plan"",""Project Management Plan""},B2))" '<- Edit as required (note double quotes)
  Range("$A$1:$N$300").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Z1:Z2"), Unique:=False
  Range("Z2").ClearContents
End Sub


Sub Clear_AdvancedFilter()
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub


Sub Using_AutoFilter()
  Dim a As Variant, vals As Variant, itm As Variant
  Dim s As String
  Dim i As Long
  
  vals = Split("PMP|PM Plan|Project Management Plan", "|")  '<- Edit as required
  With Range("$A$1:$N$300")
    a = .Columns(2).Value
    For Each itm In a
      For i = 0 To UBound(vals)
        If InStr(1, itm, vals(i), 1) Then
          s = s & "|" & itm
          Exit For
        End If
      Next i
    Next itm
    .AutoFilter Field:=2, Criteria1:=Split(Mid(s, 2), "|"), Operator:=xlFilterValues
  End With
End Sub