Dynamic multiple Autofilter Criteria for Excel using VBA

Question

We have a tracking list with product IDs in Excel and we frequently have to compare entries for several products using this tracking list. We use the .AutoFilter, search for the ID then click on "add to current selection". We repeat that N times. I want to automate this using VBA.

I have constructed an Input collector and as far as I can understand I need to collect the data in an Array.

Here a screenshot of a test worksheet.

And here a test code that is gets an array and plugs it into .AutoFilter with 'xlFilterValues' this does yield not the desired outcome but rather and empty list.

Sub Multifilter()

Dim FilteredRNG As Range
Dim TestAR(4) As Long
TestAR(0) = 100034
TestAR(1) = 165738
TestAR(2) = 165510
TestAR(3) = 165512
TestAR(4) = 165567

Set FilteredRNG = Sheet2.Range("B1:B29") ' Get my test range

FilteredRNG.AutoFilter Field:=1, Criteria1:=TestAR, Operator:=xlFilterValues
End Sub

Result is here:

If I switch the Operator to xlOr the Result changes to:

My Array looks good during debugging:

So how do I get N IDs selected from the ID list using an array?

Answer 1

I figured it out. The array needs to be a string in order for it to work.

Dim TestAR(4) As String 

Solves the issue.