Filter with more than one criteria.. Begins With and equals too

[ronanbaker1]

Hi,

I need a macro that will filter all properties starting with SA.
I also need to someway link in specific properties to the filter. Ie BTA, DSA, etc there are many....

Thanks

Ronan

 

[sergioMabres]

Ronan,
Filters can be add up to 2 in a OR or AND relation

    Selection.AutoFilter
    ActiveSheet.Range("$C$1:$C$15").AutoFilter Field:=1, Criteria1:="=gta", _
        Operator:=xlOr, Criteria2:="=sa*"

For more than 2 you will need to use: Advanced Filter
Where you can have as many criteria as you need

Sergio

 

[ronanbaker1]

Yes I need more than one... Could u code an advance filter and il try and work of that tomorrow..

Appreciate it

 

[James Snyder]

Autofilter can handle quite a few criteria, but you have to pass them as an array to Criteria1. As an example:

Private Function PSIDDupeCheck(ByRef wkBook As Workbook, _
    ByRef wkSheet As Worksheet, _
    ByVal wkColumn As Long, _
    ByRef exceptArray() As String, _
    ByRef sendDate As String, _
    ByVal errorSrc As String, _
    Optional ByVal param1 As String, _
    Optional ByVal param2 As String, _
    Optional ByVal param3 As String, _
    Optional ByVal param4 As String) As String

  <code removed="">
      ' Set AutoFilter to screen out non-compared objects
    .AutoFilter Field:=wkColumn, Criteria1:=Array( _
        param2, _
        param3, _
        param4), _
        Operator:=xlFilterValues
    <code removed="">

    wkSheet.AutoFilterMode = False
End Function

</code></code>

 

[sergioMabres]

Here is the code

Sub advancefilter()

    Range("A6").Select
    Range("A5:D32").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("F5:F8"), Unique:=False
End Sub

As you can see I used 3 criteria but you can use 100s it is just a range for criteria

Here is the sample file so you can test the advanced filter
https://dl.dropboxusercontent.com/u/23094164/advfilter1.xlsm
I hope this helps
Sergio

 

[ronanbaker1]

Hi,

Hi Sergio, thanks but I cant seem to get that to work either. Really didn't think I would have as much trouble with this.

James, This is what I was initially doing in my Macro. But what I want is the Array for specific Names in Criteria1 and Also have it for all 'SA*' and all 'PR*'

For more detail.

ActiveSheet.Range("$A$1:$Q$5000").AutoFilter Field:=1, Criteria1:=Array( _
"SA*", "PR*", "DSCA", "DSCACBB", "DSCAFMM", "DSCAMM", "MVA3", "PALACGA", "PALACSAD", "PALACST", "PAR3C", "PAR3CORP", "PARTNJ2", "PEG", "PLAZIA", "PLNJSEL", "PLREAL", "PREPALAC", "PREPALCOL", "PREPLNJ", "PREPRIAC", "PRETCB", "UNIONCAR", "VANTAGE", "PLAZMM", "PLAZIL"), Operator:=xlFilterValues


But this Macro doesn't Include my SA* or PR*...... It will include the rest.

Don't know if this is possible but could you have it like this?

ActiveSheet.Range("$A$1:$Q$5000").AutoFilter Field:=1, Criteria1:=Array( _
"DSCA", "DSCACBB", "DSCAFMM", "DSCAMM", "MVA3", "PALACGA", "PALACSAD", "PALACST", "PAR3C", "PAR3CORP", "PARTNJ2", "PEG", "PLAZIA", "PLNJSEL", "PLREAL", "PREPALAC", "PREPALCOL", "PREPLNJ", "PREPRIAC", "PRETCB", "UNIONCAR", "VANTAGE", "PLAZMM", "PLAZIL"), Operator:=xlFilterValues

'And then below have another filter that says... 'Add in all SA*

'And then below have another filter that says... 'Add in all PR*

 

[Peter_SSs]

Try this Advanced Filter code in a copy of your workbook.

Sub Adv_Fltr()
  Dim aVals
  Const sVals As String = "=SA*,=PR*,=DSCA,=DSCACBB,=DSCAFMM,=DSCAMM,=MVA3," _
          & "=PALACGA,=PALACSAD,=PALACST,=PAR3C,=PAR3CORP,=PARTNJ2," _
          & "=PEG,=PLAZIA,=PLNJSEL,=PLREAL,=PREPALAC,=PREPALCOL,=PREPLNJ," _
          & "=PREPRIAC,=PRETCB,=UNIONCAR,=VANTAGE,=PLAZMM,=PLAZIL"
  
  aVals = Split(sVals, ",")
  Columns("R").Insert
  Range("R1").Value = Range("A1").Value
  With Range("R2").Resize(UBound(aVals) + 1)
    .NumberFormat = "@"
    .Value = WorksheetFunction.Transpose(aVals)
  End With
  Range("A1:Q5000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
      Range("R1").Resize(UBound(aVals) + 2), Unique:=False
  Columns("R").Delete
End Sub

I've left them in the list but note that "PREPALAC", "PREPALCOL", "PREPLNJ", "PREPRIAC", "PRETCB" are superfluous since they would be covered by the "PR*" condition.

 

[ronanbaker1]

Hi Peter,

This helps a lot!!!

One Last question on this if possible...

Could I still have my Filter buttons shown on top. As in what has been Fitlered with the Arrow Buttons going down.

 

[Peter_SSs]

Could I still have my Filter buttons shown on top. As in what has been Fitlered with the Arrow Buttons going down.

No, AutoFilter and Advanced Filter are different beasts and don't tolerate each other.

 

[ronanbaker1]

Hi Peter,

Although I thought it would be the same if I changed some data it it not. Sorry to be bothering you about this but could you make this work?
Dim aVals
Const sVals As String = "=SA*,=PRIAC*,=BAT,=COMFUT,=DCP,=GICFUND,=GISACOMM," _
& "=GPSA, =GMSA, =GRPAN, =HICKORY, =IADEF, =IAPAY," _
& "=PARCC, =PARCCORP, =PARTCORP, =PARTCUST, =PARU, =PARUNJTR1, =PLAZTR1, =PLAZTR2, =PLNJMM, =PLNJTR1, =PLNJTR2, =PLUSUSD, =PPBO, =PRIVEST, =PRIVESTPLUS, =SEA, =SEASIADCP2, =UPARCC," _
& "=UPLAZTR1, =UPLAZTR2"

aVals = Split(sVals, ",")
Columns("R").Insert
Range("R1").Value = Range("A1").Value
With Range("R2").Resize(UBound(aVals) + 1)
.NumberFormat = "@"
.Value = WorksheetFunction.Transpose(aVals)
End With
Range("A1:Q5000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("R1").Resize(UBound(aVals) + 2), Unique:=False
Columns("R").Delete



What is happening is that it is filtering some values but not all of them? And my spelling is correct.