VBA autofilter using an array

[mecerrato]

I am using the below code to try and filter out these values but I don't know how to tell it to not equal these values.
My end goal is to show all values except the ones listed in the array, can anyone help me figure this out?

     ActiveSheet.Range("$a$10:$cr$500").AutoFilter Field:=8, Criteria1:=Array( _
     "*HOME*", "*BOND*", "In Origination", "*FHA*"), Operator:=xlFilterValues

 

[Peter_SSs]

Unfortunately there isn't a simple way to exclude more than two items. You could try this code. I have assumed that it was deliberate that "In Origination" does not include asterisks like the others.
See if it does what you want.

Sub AF_NotIncludingMany()
  Dim RX As Object, d As Object
  Dim a As Variant
  Dim i As Long
 
  Const WildcardExclusions As String = "HOME|BOND|FHA" '<- Add more if req'd. Note: "|" is not at start or end
  Const ExactExclusions As String = "|In Origination|XYZ|" '<- Add more if req'd. Note: "|" is at start and end
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = WildcardExclusions
  With Range("$A$10:$CR$500")
    a = .Columns(8).Value
    For i = 2 To UBound(a)
      If InStr(1, ExactExclusions, "|" & a(i, 1) & "|", vbTextCompare) = 0 And Not RX.Test(a(i, 1)) Then d(a(i, 1)) = 1
    Next i
    If d.Count > 0 Then .AutoFilter Field:=8, Criteria1:=d.Keys, Operator:=xlFilterValues
  End With
End Sub

 

[mecerrato]

Unfortunately there isn't a simple way to exclude more than two items. You could try this code. I have assumed that it was deliberate that "In Origination" does not include asterisks like the others.
See if it does what you want.

Sub AF_NotIncludingMany()
  Dim RX As Object, d As Object
  Dim a As Variant
  Dim i As Long
 
  Const WildcardExclusions As String = "HOME|BOND|FHA" '<- Add more if req'd. Note: "|" is not at start or end
  Const ExactExclusions As String = "|In Origination|XYZ|" '<- Add more if req'd. Note: "|" is at start and end
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = WildcardExclusions
  With Range("$A$10:$CR$500")
    a = .Columns(8).Value
    For i = 2 To UBound(a)
      If InStr(1, ExactExclusions, "|" & a(i, 1) & "|", vbTextCompare) = 0 And Not RX.Test(a(i, 1)) Then d(a(i, 1)) = 1
    Next i
    If d.Count > 0 Then .AutoFilter Field:=8, Criteria1:=d.Keys, Operator:=xlFilterValues
  End With
End Sub

That is a beautiful solution to an annoying issue, thanks so much for doing this worked perfectly!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.