More than 2 criterias in an autofilter

**machado** · 03-20-2010, 11:41 AM

Hi there!

I've all morning trying to resolve this so please help me...

All I need is just to have 3 or more different criterias in an autofilter.

I tried this but it wont work...

please give a hand!

Thanks!

Code:

    Dim a, b, c, a1, b1, c1 As String

    a = Cells(5, 17).Value
    b = Cells(6, 17).Value
    c = Cells(7, 17).Value

    a1 = Cells(5, 18).Value
    b1 = Cells(6, 18).Value
    c1 = Cells(7, 18).Value

    ....

    ActiveSheet.Range("$A$4:$N$15132").AutoFilter Field:=1, Criteria1:=a, Operator:=xlOr, Criteria2:=b, Operator:=xlOr, Criteria3:=c

    ActiveSheet.Range("$A$4:$N$15132").AutoFilter Field:=3, Criteria1:=a1, Operator:=xlOr, Criteria2:=b1, Operator:=xlOr, Criteria3:=c1

**royUK** · 03-20-2010, 11:50 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Code Tags added this time

**machado** · 03-20-2010, 12:40 PM

Please tell why this works

Code:

    ActiveSheet.Range("$A$4:$N$15131").AutoFilter Field:=1, Criteria1:=Array("154", "155", "156", "157"), Operator:=xlFilterValues

and this doesn't

Code:

Dim a, b, c, d As String

a = 154
b = 155
c = 156
d = 157


    ActiveSheet.Range("$A$4:$N$15131").AutoFilter Field:=1, Criteria1:=Array(a, b, c, d), Operator:=xlFilterValues

**mrice** · 03-20-2010, 01:26 PM

Your dim statement has only declared variable d as a string. In the absence of a variable type, the variant type is used by default. This means that a, b and c behave like integers.

Try using

Code:

Dim a As String, b As String, c As String, d As String

**Palmetto** · 03-20-2010, 01:36 PM

Excel versions prior to 2007 only support two criteria and arrays, as far as I know, cannot be used within Auto-Filter. Don't have Excel 2007 on this PC, so, not sure if it accepts use of arrays - but it does support many more criteria than prior versions.

If the array approach does not work, it would be quite simple and just as efficient to pass the string variables into a range of cells and specify that range as criteria for Advanced Filter.

**broro183** · 03-20-2010, 02:01 PM

hi Machado,

Welcome to the forum

I think you should have put this post in your previous thread because it seems to be the same issue to me. Hopefully a Mod will spot this & they can merge the two threads...

Edit: My connection's playing up today (again!) so I'm sorry about the double up of info - see Martin's previous post for the same comments about your variable declarations. /end edit.

I couldn't see any relevant* information in either the VBE or Excel's 2007 Help files for Autofilter but perhaps the following may help...
*Unless, do you have a mixture of formats in the column to be filtered (eg string & number)?

Code:

Dim a, b, c, d As String
'the above line of code is the equivalent of the below
Dim a as variant, b as variant, c as variant, d As String
'when I think your intent is more along the lines of...
Dim a As String, b As String, c As String, d As String

If you need more than two criteria, as you've mentioned in the previous thread, then I suggest you use a Helper column containing a formula which returns a True or False (perhaps an If statement) if any of the criteria are fulfilled.

hth
Rob