With the help of others in this forum, I've almost completed my coding project.
I have a list in column "B" which includes:
Pending (Responded)
Assigned
Closed - No Action Needed
Available
When I run the loop portion of this sub, I get the following string result:
str = "Pending (Responded)", "Assigned", "Closed - No Action Needed", "Available"
Now I need help using this string for the Criteria1 part of the AutoFilter method
When I use this: str = Array(res), I get the Type mismatch error
When I use this: str = "Array(" & res & ")", it doesn't filter anything and hides all the rows.
Any help is appreciated
Sub Try_This()
Dim res As Variant, i As Long
Dim S, str As Variant
res = Chr(34)
For i = 2 To Sheets("Control_Info").Cells(Rows.Count, 2).End(xlUp).Row
If Len(Sheets("Control_Info").Cells(i, 2).Offset(1)) <> 0 Then
res = res & Sheets("Control_Info").Cells(i, 2).Value & Chr(34) & ", " & Chr(34)
Else
res = res & Sheets("Control_Info").Cells(i, 2).Value & Chr(34)
End If
Next i
Sheets("Control_Info").Range("B200").Value = res
'str = "Array(" & res & ")"
str = Array(res)
'Debug.Print str
Sheets("Working_Data").Select
Selection.AutoFilter Field:=6, Criteria1:=str, Operator:=xlFilterValues
End Sub
Bookmarks