VBA required for AutoFilter XLFilterValues Array

Question

I'm new to VBA and I'm trying to create a macro that will filter a column on one sheet (Rules) based on the cell value in another which contains the unique values on that column. The unique values are separated by " & ":

Example, a cell may contain the following value: 19.1 & 19.2 & 19.2c & 14.3a & 14.3b & 14.3b(1) & 14.3b(2) & 14.3b(3) & 14.3c & 14.3c(1) & 14.3c(2) & 14.7a

Each of these values has a unique row in column C of the Rules sheet and I want to filter the sheet on that column for all these values.

I have tried the following but it doesn't work:

Sub ArrayFilter()
Dim Rules As String
Dim ArrayFilter As String

Dim arr As Variant

'This is the cell with the values
' e.g 19.1 & 19.2 & 19.2c & 14.3a & 14.3b & 14.3b(1) & 14.3b(2) & 14.3b(3) & 14.3c & 14.3c(1) & 14.3c(2) & 14.7a
Rules = ActiveCell.Offset(0, 38).Range("A1").Value
' I'm trying to convert this to a string for the filter crieria
ArrayFilter = Chr(34) & Replace(Rules, " & ", Chr(34) & ", " & Chr(34)) & Chr(34)
'Now I make this an Array
arr = Array(ArrayFilter)


Sheets("2019 Rules Breakdown").Select
Application.Run "RemoveAndReApplyFilters"
Range("C1").Select

ActiveSheet.ListObjects("Table10").Range.AutoFilter Field:=3, Criteria1:=arr, _
        Operator:=xlFilterValues
        
'This fails, when I look at the filter deployed it is using Equals and just has the arr output.

End Sub

Any help would be gratefully welcomed.

Thanks

Please, try simple: `arr = Split(ActiveCell.Offset(0, 38).Range("A1").Value, " & ")` and you will obtain the necessary array... `Rules` and `ArrayFilter` variables are unnecessary. — FaneDuru, Nov 25 '20 at 12:21
I meant, you do not obtain an array as you wish/believe/need using your way. The array will have only one element, respectively: `"19.1", "19.2", "19.2c", "14.3a", "14.3b", "14.3b(1)"` and so on. You can test the obtained array using `Debug.print Join(arr, "|")` — FaneDuru, Nov 25 '20 at 12:32
Fabulous! Worked a treat. Thank you so much for your speedy response and support, regards Fraz. — FrazNic, Nov 25 '20 at 12:33
Glad I could help! I will transform the comment in an answer, adding some relevant way to check... — FaneDuru, Nov 25 '20 at 12:34

score 0 · Answer 1 · answered Nov 25 '20 at 12:43

The way you try obtaining the array is not appropriate. You can check it in this way:

 Dim rules As String, ArrayFilter As String, arr
 rules = "19.1 & 19.2 & 19.2c & 14.3a & 14.3b & 14.3b(1)"

 ArrayFilter = Chr(34) & Replace(rules, " & ", Chr(34) & ", " & Chr(34)) & Chr(34) 
 arr = Array(ArrayFilter)

 Debug.Print Join(arr, "|"), UBound(arr) 'it will return "19.1", "19.2", "19.2c", "14.3a", "14.3b", "14.3b(1)"    0 .
                                         'meaning an array with a single element, the long string...
End Sub

Now, using the next way, for the same string, the array will look like it should:

Sub testArrayFromString()
 Dim rules As String,  arr1

 rules = "19.1 & 19.2 & 19.2c & 14.3a & 14.3b & 14.3b(1)"
 arr1 = Split(rules, " & ")
 Debug.Print Join(arr1, "|"), Ubound(arr1) '19.1|19.2|19.2c|14.3a|14.3b|14.3b(1)       5 
                                           'proving that it is an array with 6 elements (0 based)
            'Being in VBE, press `Ctrl + G` to see the result in `Immediate Window`.
End Sub

VBA required for AutoFilter XLFilterValues Array

1 Answers1