Autofilter with multiple criteria using an array of ranges

Question

Coming from the question Excel macro filter based on multiple cell values and using the answer from Creating an Array from a Range in VBA

I was trying to create an autofilter for a wide range using an array. However, it keeps using only the latest value in the range of the array.

How can I use all of the values in the filter?

Sub Test()

Dim DirArray As Variant
DirArray = [A2:A5].Value2
ActiveSheet.range("$B$1:$C$10").AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=DirArray

End Sub

This may help to answer your question: https://stackoverflow.com/questions/38404176/using-string-array-as-criteria-in-vba-autofilter — 5202456, Jul 06 '18 at 08:56

score 8 · Accepted Answer · answered Jul 06 '18 at 10:09

Recording a Macro while applying a filter using multiple numeric value reveals that the filter is expecting an array of string.

ActiveSheet.Range("$B$1:$C$11").AutoFilter Field:=2, Criteria1:=Array("1", "2", "3"), Operator:=xlFilterValues

Notice that data = [A2:A5].Value returns a 2D array

Let's flatten the 2D array: data = [A2:A5].Value

Now that we have a 1D array we need to convert it to an Array of String.

We can do this by first creating a delimited string

 Data = Join(Data, ",")

And then Splitting that string

   ActiveSheet.Range("$B$1:$C$11").AutoFilter Field:=2, Operator:=xlFilterValues, _
Criteria1:=Split(Join(Application.Transpose(Range("A2:A6")), ","), ",")

TextJoin() really simplifies the process.

    ActiveSheet.Range("$B$1:$C$11").AutoFilter Field:=2, Operator:=xlFilterValues, _
Criteria1:=Split(WorksheetFunction.TextJoin(",", True, Range("A2:A6")), ",")

Good complete answer: I am glad it brings you to 1000 points :D — Pierre44, Jul 06 '18 at 13:19

score 2 · Answer 2 · answered Jul 06 '18 at 11:20

Without seeing your data, I can see one conflicting issue in your code and the possibility of another conflict with your data.

The AutoFilter is expecting a simple 1-D array for Criteria1 with operator:=xlfiltervalues. You are passing in a 2-D array. Just use application.transpose to convert the incoming data from 2-D to 1-D.
```
DirArray = Application.Transpose(.Range("A2:A5").value2)
```
DirArray doesn't sound like a variable that was created to store numbers but it's worth mentioning that AutoFilter doesn't like an array of true numbers being passed into Criteria1; it expects text-that-look-like-numbers. After collecting the values, run through them and convert true numbers to text facsimiles.
```
DirArray = Application.Transpose(.Range("A2:A5").value2)
For i = LBound(DirArray) To UBound(DirArray)
    DirArray(i) = CStr(DirArray(i))
Next i
```

So with those two issues dealt with, you should have no problem passing in an array as criteria.

Option Explicit

Sub Test()

    Dim DirArray As Variant, i As Long

    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False

        DirArray = Application.Transpose(.Range("A2:A5"))
        For i = LBound(DirArray) To UBound(DirArray)
            DirArray(i) = CStr(DirArray(i))
        Next i
        .Range("$B$1:$C$10").AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=DirArray
    End With

End Sub

score 0 · Answer 3 · 2018-07-06T10:08:32.103

0

You can use this:

Sub arraytest2()

Dim DirArray As Variant

DirArray = ActiveSheet.Range("A1:A5")

ActiveSheet.Range("$B$1:$C$10").AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=Array(ActiveSheet.Range("A" & LBound(DirArray) & ",A" & UBound(DirArray)))

End Sub

edited Jul 06 '18 at 10:08

answered Jul 06 '18 at 09:01

It also filters only with the last value of the Range A – Pierre44 Jul 06 '18 at 09:11
Sorry, I didn't undertood your question properly, I made some edits.. is this what you were looking for? – Jul 06 '18 at 09:14

Autofilter with multiple criteria using an array of ranges

3 Answers3

Linked