Advance AutoFilter to exclude specific values

Question

I want to filter a large list of names in a Sheet in excel. In another sheet, I contained a list of names that I want to filter out and exclude from the larger list. How can I use the advanced filter for this? I tried this below, but it does not work. My large list is in K2:K5000, and my criteria are in H2:H3(the criteria will grow, but I kept the list small for testing). Any help would be greatly appreciated!

Sub Filter()
    Sheet5.Range("K2:K5000").AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Sheet3").Range("H2:H3"), Unique:=False
End Sub

Answer 1

H2:H3 K2:K5000 , :

• , K1 ( )
• (, I1:I2)
• I1 blank

• I2

   =ISNA(MATCH(K2,$H$2:$H$3,0))
  

•  Sheet5.Range("K1:K5000").AdvancedFilter Action:=xlFilterInPlace, _
       CriteriaRange:= Sheets("Sheet3").Range ("I1:I2"), Unique:=False 
  

Answer 2

, , ( ). , , ( , ).

Sub Filter()
Dim i as integer
Dim str as string
Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

With Worksheets("Sheet3")
    For i = 2 To 3
        str = CStr(.Range("H" & i).Value)
        If Not dict.exists(str) Then
            dict.Add str, vbNullString
        End If
    Next i
End With

With Sheet5
    For i = 2 To 5000
        str = CStr(.Range("K" & i).Value)
        If Len(str) > 0 And dict.exists(str) Then
            .Range("K" & i).EntireRow.Hidden = True
        Elseif
            'alternatively, you can add those that aren't found
            'to an array for autofilter
        End if
    Next i
End With

'If building autofilter array, apply filter here.

End Sub

AutoFilter:

"Operator: = xlFilterValues" AutoFilter. , , , for ( , ).

. , .

With Sheet5
    .AutoFilterMode = False
    .Range("K1").AutoFilter _
      Field:=1, _
      Criteria1:=arr, _
      Operator:=xlFilterValues
End With

Answer 3

, , Advance.
, .

, , . , :

, , Data1 Data2. , :

. . , . (B6). TRUE FALSE.

, A11 ( , ).
B2, A2, .
: =A11<>"Data1".

Data1, Data2.
C2, : =A11<>"Data2"

Advance Filter . , , :

With Sheets("Sheet1")
    .Range("A10:A20").AdvancedFilter xlFilterInPlace, .Range("A1:C2")
End With

! Data1 Data2.

:

, , , , .
- :-). .

:

, , . , Data1 Data2 > in H2:H3 Sheet2, B2 C2 : =A11<>Sheet2!H2 =A11<>Sheet2!H3 .