Delete data if it exists elsewhere on the sheet

[TheWennerWoman]

Hello,

I hope I can explain this properly.

I have new data that gets pasted into columns A-E.

I have static data (also five columns) in columns H-L.

When the new data is processed, it gets copied by a macro to the bottom of the range H-L.

What I'd like to do is, before I process anything, delete anything from A-E that already exists in H-L (because the fact that it's in H-L means I've processed it previously).

Somebody on here very kindly provided this previously which works if column A exists in column B

For r = lRow1 To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("B:B"), Cells(r, "A")) > 0 Then
        Cells(r, "A").Delete Shift:=xlUp
    End If
Next r

but I need this expanded to delete five columns, not one.

Any help greatly appreciated as always.

EDIT: column C or column E will always be unique - does that make it easier, just searching for one of those values?

 

[kvsrinivasamurthy]

Try

For r = lRow1 To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("B:B"), Cells(r, "A")) > 0 Then
        Range(Cells(r, "A"), Cells(r, "E")).delete Shift:=xlUp
    End If
Next r

 

[TheWennerWoman]

Try

For r = lRow1 To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("B:B"), Cells(r, "A")) > 0 Then
        Range(Cells(r, "A"), Cells(r, "E")).delete Shift:=xlUp
    End If
Next r

Many thanks

I can amend that ever so slightly to work for me, much appreciated!