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

ronanbaker1

Board Regular
Joined
Nov 15, 2012
Messages
75
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Ronan,
Filters can be add up to 2 in a OR or AND relation

Code:
    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

Board Regular
Joined
Nov 15, 2012
Messages
75
Yes I need more than one... Could u code an advance filter and il try and work of that tomorrow..

Appreciate it
 

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
Autofilter can handle quite a few criteria, but you have to pass them as an array to Criteria1. As an example:
Code:
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

Well-known Member
Joined
Feb 24, 2013
Messages
946

ADVERTISEMENT

Here is the code
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

Board Regular
Joined
Nov 15, 2012
Messages
75
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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.
 
Last edited:

ronanbaker1

Board Regular
Joined
Nov 15, 2012
Messages
75
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 15, 2012
Messages
75
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top