Controlling Autofilter with VBA - adding criteria to existing filtered column

[coloradomtns]

Hello all,

I am stuck on an autofilter issue. I am trying to add criteria to an existing filtered column named "Interval".

I have an Excel table with a column containing string or numeric data (see table below).

I need to filter and return all records that are contained in the array (note I do not need to use an array).

The code below only returns records with B1 and 7.

Interval
A1,B1
7,8
B1
A1,C1
2,3,4
6A,6E,6F
A1,B1,2,3,4,5
6A,6B,6C
A1,B1,2,3,4,5,6,7,8
7
2,3,4
2
5,6A,6E,7,8
5

<tbody>
</tbody>

Here is my code that I'm currently using below. It only returns B1 and 7 - need to see all records containing 6A, B1, and 7.

=========================
Sub Macro5()
Dim i As Long, arr As Variant

arr = Array("6A", "B1", "7")

ActiveSheet.Range("A1:A15").AutoFilter field:=1, Criteria1:=arr, Operator:=xlFilterValues

End Sub

Thanks!

 

[crazydragon84]

i don't think you can autofilter more than 2 criteria at a time. If your filter values are always constant, i suggest you create a helper column (X or Yes or whatever), and filter on that column

 

[Fluff]

You can filter on multiple values with an array, BUT it will only work when you filter on =
You need to filter on Contains & are therefore limited to 2 values as mentioned by crazydragon84.
Have a look at advanced filters instead.
http://www.contextures.com/exceladvancedfiltervba.html

 

[coloradomtns]

Thank you both. I kind of knew that was going to be the answer but I had hopes I was missing something. I abandoned the autofilter approach and used 2 nested FOR loops with 6 nested IF statements. Works perfect - but I'm glad I asked the autofilter question now and have your answers for future reference.

 

[Fluff]

Glad you got it sorted & thanks for the feedback