Dear all,
how to set the array as variable
Cells(1, 1).Value = "10", "11", "12","1", "2", "3"ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Array(Sheets("Summary").Cells(1, 1).Value), Operator:=xlFilterValues
Dear all,
how to set the array as variable
Cells(1, 1).Value = "10", "11", "12","1", "2", "3"ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Array(Sheets("Summary").Cells(1, 1).Value), Operator:=xlFilterValues
If your values in Cells(1,1) have the "" then maybe:
Else:ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Replace(Sheets("Summary").Cells(1, 1).Value, """", ""), ", "), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Sheets("Summary").Cells(1, 1).Value, ", "), Operator:=xlFilterValues
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