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?

You need to use Operator:=xlFilterValues to filter by an array of values. • Also you should remove the parenthesis from GetArray (varArray) and GetFilteredRNG (FilteredRNG) as they do something different than you think they do. Use GetArray varArray or put a Call statement infront Call GetArray(varArray). As you have them now they will convert the default ByRef parameter into a ByVal submission. You can see that because there is a space between the function name GetArray and the parenthesis. If that's the case it is a conversion to ByVal and the parenthesis needs removed. — Pᴇʜ
– Pᴇʜ, Commented Mar 24, 2022 at 7:11
Operator:= xlFiltervalues doesn't work either. FYI: the GerArray and GetFilterRNG are dummy subs that I use to make this code StackOverFlow readable. In my code I have it all spaghettied, but thanks for the info I didn't know that. — Lucas Raphael Pianegonda
– Lucas Raphael Pianegonda, Commented Mar 24, 2022 at 7:33
Well then please show a minimal reproducible example of your issue. Because with an proper array and Operator:=xlFilterValues it works. Give some example data for your array and some example data you try to filter. — Pᴇʜ
– Pᴇʜ, Commented Mar 24, 2022 at 8:19
Give me a minute, I'll update the post with some test data and then it should be more clear. — Lucas Raphael Pianegonda
– Lucas Raphael Pianegonda, Commented Mar 24, 2022 at 12:51

Lucas Raphael Pianegonda · Accepted Answer · 2022-03-24 13:13:17Z

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.

answered Mar 24, 2022 at 13:13

Lucas Raphael Pianegonda

1,18112 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic multiple Autofilter Criteria for Excel using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related