Advanced Filter

[Jvalades]

Hellloooo Friends.. Can someone help me debug my Advanced filter.

My end goal is to hide all rows that do no have "ABCD-201,ABCD-202,ABDC-203" in column A.

 'Start my filters
      
    'creating a Advanced filter to find ABCD
    With Sheets(2)
    
    .Range("AR1").NumberFormat = "General"
        'Insert values of interest in cells AP1
        .Range("AP1").Value = "ABCD"
        
    'Create named range MyList
    .Names.Add Name:="MyList", RefersTo:="='SO'!$AP$1"
    
    'Put a header in AQ1
    .Range("AQ1").Value = "MyList"
     
     'Create the criteria range in range AR1 using a formula
        .Range("AR1").Formula = "=NOT(OR(INDEX(ISNUMBER(SEARCH(MyList,A2)),0)))"
        
        'Apply advanced filter in data range
        .Range("$A$1:$A$" & .Cells(.Rows.count, "A").End(xlUp).Row). _
            AdvancedFilter Action:=xlFilterInPlace, criteriarange:=.Range("AR1"), _
            Unique:=False
    End With

 

[Marcelo Branco]

See if this does what you are looking for

Sub aTest()
    
    With Sheets(2)
        'Put a header in AP1
        .Range("AP1") = "Mylist"
        'Insert values of interest in cells AP2, AP3,...
        .Range("AP2").Value = "ABCD-201"
        .Range("AP3").Value = "ABCD-202"
        .Range("AP4").Value = "ABCD-203"
        
        'Create named range MyList
        .Range("AP2:AP4").Name = "Mylist"
     
        If .FilterMode Then .ShowAllData
        
        'Create the criteria range in range AR1:AR2 using a formula
        'leave blank the cell above the formula
        .Range("AR1") = ""
        'insert the formula in AR2 - assumes data beginning in row 2, header in row 1
        .Range("AR2").Formula = "=ISNUMBER(MATCH(A2,MyList,0))"
        
        'Apply advanced filter in data range
        .Range("$A$1:$A$" & .Cells(.Rows.Count, "A").End(xlUp).Row). _
            AdvancedFilter Action:=xlFilterInPlace, criteriarange:=.Range("AR1:AR2"), _
            Unique:=False
    End With
End Sub

M.