vba - Advance AutoFilter to exclude certain values

Question

I want to filter a large list of names in a Sheet in excel. In another sheet I have contained a list of names that I want to filter out and exclude from the larger list. How would I use the advanced filter to do this? I have tried this below but it is not seeming to work. My big list is in K2:K5000 and my criteria is 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

To exclude the values in H2:H3 from K2:K5000 using advanced filter you can use following approach:

• Make sure cell K1 is not empty (enter any header)
• Find 2 unused cells (e.g. I1:I2)
• Leave I1blank

• Enter the following formula in I2

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

• Use the following code to exclude rows

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