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.

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Interval
[/TD]
[/TR]
[TR]
[TD]A1,B1
[/TD]
[/TR]
[TR]
[TD]7,8
[/TD]
[/TR]
[TR]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]A1,C1
[/TD]
[/TR]
[TR]
[TD]2,3,4
[/TD]
[/TR]
[TR]
[TD]6A,6E,6F
[/TD]
[/TR]
[TR]
[TD]A1,B1,2,3,4,5
[/TD]
[/TR]
[TR]
[TD]6A,6B,6C
[/TD]
[/TR]
[TR]
[TD]A1,B1,2,3,4,5,6,7,8
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[/TR]
[TR]
[TD]2,3,4
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5,6A,6E,7,8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

























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