vba - Delete all rows except names contains in two lists

[Mallesh23]

Hi Team,

I have two lists of Actors and Actress,
task is delete all actors and Actress whose name not in any of the list.

looking for answer in loop or array or dictionary , auto filter.

Below is Exclude list , Rest delete

ColumnA Column B

Actor	actress
Amitabh	Deepika Padukone
Anil Kapur	Madhuri
Salman Khan	Juhi Chawala
Shahrukh Khan	Karishma Kapoor
Hritik Roshan	Priyanka Chopra

Thanks
mg

 

[Jaafar Tribak]

How about:

Sub Test()

    Dim ar() As String, rng As Range, i As Long
    
    ar = Split("Amitabh,Deepika Padukone,Anil Kapur,Madhuri,Salman Khan,Juhi Chawala,Shahrukh Khan,Karishma Kapoor,Hritik Roshan,Priyanka Chopra", ",")
    Set rng = Sheet1.Range(Sheet1.Range("c2:d2"), Sheet1.Range("c" & Rows.Count & ":" & "d" & Rows.Count).End(xlUp))
    
    For i = 1 To rng.Rows.Count
        If IsError(Application.Match(rng.Cells(i, 1), ar, 0)) And IsError(Application.Match(rng.Cells(i, 2), ar, 0)) Then
            If Len(rng.Cells(i, 1) & rng.Cells(i, 2)) Then
                rng.Cells(i, 1).EntireRow.Delete
            End If
        End If
    Next i

End Sub