Filter to include everything except the values of an array

[Tablecloth98]

I've got an array containing values that are to be excluded when I filter my data. I've tried the code below but keep getting a type mismatch error. Does anybody know where I'm going wrong?

Dim criteriaRange As Range

Dim criteriaArray As Variant

Set criteriaRange = ThisWorkbook.Sheets("Data Val").Range("L11:L20")

criteriaArray = Application.Transpose(criteriaRange.Value)

 

ThisWorkbook.Sheets("DATA").Range("$A$6:$BJ$1048576").AutoFilter Field:=41, Criteria1:="<>" & criteriaArray, Operator:=xlFilterValues, Criteria2:="<>"

 

[Fuji]

Try

Dim criteriaRange As Range

Dim criteriaArray As Variant, myCol&

Set criteriaRange = ThisWorkbook.Sheets("Data Val").Range("L11:L20")

myCol = 41

With ThisWorkbook.Sheets("DATA").Range("$A$6:$BJ$1048576")
    criteriaArray = Filter(.Parent.Evaluate(Replace("transpose(if(isna(match(#," & _
    criteriaRange.Address(, , , True) & ",0)),#))", "#", .Columns(myCol).Address)), False, 0)
    .AutoFilter myCol, criteriaArray, 7
End With

 

[Tablecloth98]

Try

Dim criteriaRange As Range

Dim criteriaArray As Variant, myCol&

Set criteriaRange = ThisWorkbook.Sheets("Data Val").Range("L11:L20")

myCol = 41

With ThisWorkbook.Sheets("DATA").Range("$A$6:$BJ$1048576")
    criteriaArray = Filter(.Parent.Evaluate(Replace("transpose(if(isna(match(#," & _
    criteriaRange.Address(, , , True) & ",0)),#))", "#", .Columns(myCol).Address)), False, 0)
    .AutoFilter myCol, criteriaArray, 7
End With

Thank you That worked perfectly!

 

[Fuji]

You are welcome and thanks for the feedback.