Array as variable

[clif]

Dear all,

how to set the array as variable




ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Array(Sheets("Summary").Cells(1, 1).Value), Operator:=xlFilterValues




Cells(1, 1).Value = "10", "11", "12","1", "2", "3"

[georgiboy]

If your values in Cells(1,1) have the "" then maybe:

ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Replace(Sheets("Summary").Cells(1, 1).Value, """", ""), ", "), Operator:=xlFilterValues

Else:

ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Sheets("Summary").Cells(1, 1).Value, ", "), Operator:=xlFilterValues

[arnelgp]

you can also create a udf and use it in your filtering:


Private Sub t()
ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=fnRangeToArray(Sheets("Summary").Cells(1, 1)), Operator:=xlFilterValues
End Sub


'arnelgp
Public Function fnRangeToArray(rng As Range, Optional ByVal delimeter As String = ",") As Variant


Dim ret() As String
Dim arr As Variant
Dim c As New Collection
Dim i As Integer, j As Integer
Dim s As String

arr = Split(rng.Value, delimeter)

For i = 0 To UBound(arr)
s = Trim$(Replace$(arr(i) & "", """", ""))
'Debug.Print s
If Len(s) Then
j = j + 1
c.Add Item:=s, Key:=j & ""
End If
Next

If c.Count <> 0 Then
ReDim ret(c.Count - 1)
For i = 1 To c.Count
ret(i - 1) = c(i & "")
Next
End If

fnRangeToArray = ret

End Function