Multiple criteria in filters, pre 2007

[Weaver]

Working in VB, is it possible to have more than 2 filter criteria in an autofilter in pre 2007 Excel?

 

[Richard Schollar]

No - you'd need to use a helper column with a formula (or Advanced filter).

 

[Weaver]

Ah, advanced filter - of course! D'oh...

 

[Jon von der Heyden]

Or, depending on what you are doing, you could store the criteria in an array and then loop through that array, assigning each item to the filter. For instance, to delete rows matching more than two criteria:

Dim varArr as Variant, lngArrItem as Long
varArr=Array("a","b","c")

For lngArrItem = Lbound(varArr) to Ubound(varArr)
    With Range("A1:A100")
        .Filter Field:=1, Criteria1:=varArr(lngArrItem)
        .Offset(1).SpecialCells(12).EntireRow.Delete
        .AutoFilter
    End With
Next lngArrItem

You could adapt for copy too...

 

[Weaver]

Nice idea Jon, in fact I think I've suggested similar myself.

My problem is they have to display at the same time tho.

Anyone know if there's an advanced filter wizard kicking around?