Hello,

I am trying to capture a list that will change everyday and store it as an array for a criteria for a filter that is in another workbook. I have been able to capture the list a few different ways, but when I set it as the criteria I keep getting errors.

This code will show the list in a msg box:

Sub ShowCriteria()
    Dim r As Range, c1 As Collection, c2 As Collection
    Dim msg As String
    Set c1 = New Collection
    Set c2 = New Collection

    Dim LastRow As Integer

    With Worksheets("Update")
        LastRow = .Range("C" & Worksheets("Update").Rows.Count).End(xlUp).Row
    End With

    On Error Resume Next
    For Each r In Range("C1:C" & LastRow)
        v = r.Value
        c1.Add v, CStr(v)
        If r.EntireRow.Hidden = False Then
            c2.Add v, CStr(v)
        End If
    Next
    On Error GoTo 0

    For i = 1 To c1.Count
        msg = msg & vbCrLf & c1.Item(i)
    Next i

    MsgBox msg
End Sub
This code I was trying to get it to apply the filter in the actual sheet:

Sub FilterSelectedCell()
Dim a() As String, iCell As Range, n As Long


Workbooks("Update.xlsm").Sheets("Update").Activate
    For Each iCell In Range("C1", Cells(Rows.Count, "C").End(xlUp))
        If iCell.Value <> "," Then
            n = n + 1
            ReDim Preserve a(1 To n)
            a(n) = iCell.Value
        End If
    Next iCell

    Workbooks("SOE").Sheets("Data").Activate
    ActiveWorkbook.Range("$A$1:$AP$400000").AutoFilter _
        Field:=7, _
        Criteria1:=a, _
        Operator:=xlFilterValues
End Sub
I'm not using both, If I can use the 2nd code and get it to work that would be great. But I keep getting the error that the Object doesn't support this property or method and it highlights the last 4 rows.