VBA - Delete entire row based on a list of values on a separate sheet

[30percent]

Hello,

I copy and paste a table "Customer" from a source to Sheet1 in excel.

I would like delete the entire row in the "Customer" table if it contains/matches values from a list in Sheet2.

I wonder if anyone has a VBA code that enable me to automate that process.

Thank you!

 

[mirabeau]

Hi,

Would you like to try this (on test data) and see if it does something like...

Sub delrows()
Dim d As Object, e, rws&, cls&, i&, j&
Set d = CreateObject("scripting.dictionary")
For Each e In Sheets("sheet2").Range("A1").CurrentRegion
    d(e.Value) = 1
Next e
Sheets("sheet1").Activate
rws = Cells.Find("*", after:=[a1], searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
cls = Cells.Find("*", after:=[a1], searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
For i = rws To 1 Step -1
    For j = 1 To cls
        If d(Range("A1").Resize(rws, cls)(i, j).Value) = 1 Then _
            Cells.Rows(i).Delete: Exit For
Next j, i
End Sub

The criteria on sheet2 should be in a contiguous area starting from cell A1.

 

[30percent]

Hi Mira,

it works perfectly.
Thank you very much!

 

[turkanet]

Hi,
How can we update codes as "look inside cell". i mean, if we search "aa", it may find also values like "aadk" or fhaa" or eaan".
thank you