Filter for Multiple Criteria, Including List and Wildcard

Question

I'm trying to create a filter on column A that will filter for both a list of data (Range("Symbols")), and for anything starting with the character ^.

This is my code right now (only including the part that refers to the filter):

Dim ar As Variant

ar = Range("Symbols")
ar = Application.Transpose(ar)
ar = Split(Join(ar, ","), ",")

With sht1.Range("A1", sht1.Range("Y" & Rows.Count).End(xlUp))

.AutoFilter Field:=1, Criteria1:=ar, Operator:=xlFilterValues, Operator:=xlOr, Criteria2:="=^*"
End With

For the above code I get the Range("Symbols") data only. The data starting with character ^ do not show.

I've also tried:

.AutoFilter Field:=1, Criteria1:=ar, Operator:=xlOr, Criteria2:="=^*"

For the above code I get all the data starting with character ^ but not the Range("Symbols") data.

And

.AutoFilter Field:=1, Criteria1:="=^*", Operator:=xlOr, Criteria2:=ar, Operator:=xlFilterValues

For the above code I get all the data starting with character ^ but not the Range("Symbols") data.

And

.AutoFilter Field:=1, Criteria1:=Array(ar, "=^*"),Operator:=xlFilterValues

The above produces a Type Mismatch error.

Answer 1

I don't think you can do that with AutoFilter but you can with Advanced Filter.

In the code below, I copy Range("Symbols") to some other area on the worksheet and then delete it (find an unused area), but you could use Range("Symbols") itself if the range header is identical to the Field1 header.

'Dim ar As Variant

'ar = Range("Symbols")
'ar = Application.Transpose(ar)
'ar = Split(Join(ar, ","), ",")

Dim rgCrit As Range
Range("symbols").Copy Range("AA2")

'copy criteria range to an unused area on the sheet
Range("AA1") = Range("A1")
Set rgCrit = Range("AA1").Resize(Range("symbols").Rows.Count + 1)

'add the ^* criteria to the range
rgCrit(rgCrit.Rows.Count, 1) = "^*"

With Sheet4.Range("A1", Sheet4.Range("Y" & Rows.Count).End(xlUp))
    .AdvancedFilter xlFilterInPlace, rgCrit
End With

'Doesn't clear filtered cells
'rgCrit.Clear
Dim c As Range
    For Each c In rgCrit
        c.Clear
    Next c


End Sub

Here are some screenshots demonstrating the before and after

Unfiltered Column

Allowed Symbols Range

Filtered Column Note the strings that start with ^ are present