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

 

[davesexcel]

Below is Exclude list , Rest delete

Delete from where?

 

[Mallesh23]

Thanks for highlighting,

The list is in sheet2 and Data is in sheet1,

my actual data is in Column C and D of sheet1
which Contains Actor and actress long list,... from that list I want to keep Criteria list. rest actor and actress row want to delete.


Thanks
mg

 

[Jaafar Tribak]

What happens if a row in one column has a valid actor\ess name and the same row in the other hasn't ?

 

[Mallesh23]

Hi Jafar,

then I don't want to delete that row...
actor actress
Salman khan, xxxx ....... I will not delete (as salman khan is in from actors list)
yyyyy Madhuri ......... I will not delete (as Madhuri is in from actress list)
xxxx yyyyy .............. I will delete ( as no actors from list and no actress from criteria list hence delete)

thanks
mg

 

[Jaafar Tribak]

So in other words, you will only delete the row if both columns are not in the exclude list ?

 

[davesexcel]

If you delete a row from an actor column, is there not a chance you would be deleting an actress that you wanted to keep?

 

[Mallesh23]

Yes , This is right :=>So in other words, you will only delete the row if both columns are not in the exclude list ?

 

[davesexcel]

Give this a go,

Sub GetErDun()
    Dim sh As Worksheet, ws As Worksheet
    Dim x As Long, LstRw As Long
    Dim Actr As Range, Actrs As Range

    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")

    With sh
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row

        For x = LstRw To 2 Step -1
            Set Actr = ws.Cells.Find(.Cells(x, 3).Value, lookat:=xlWhole)
            Set Actrs = ws.Cells.Find(.Cells(x, 4).Value, lookat:=xlWhole)

            If Actr Is Nothing Or Actrs Is Nothing Then
                .Cells(x, 1).EntireRow.Delete
            End If
        Next x
    End With

End Sub

 

[Mallesh23]

Hi Dave,
Thanks for your help, need small change in it.
It is keeping only exact match data , say sheet2 ,

Salman Madhuri .... it is keeping exact match
I want exact match as well non exact match.(either one true from Criteria list) has to keep the data.

Regards,
mg