Delete a Cell in Worksheet1 That Appears in Worksheet2

**piper23** · 10-22-2012, 07:50 PM

I have a list of emails in column A of worksheet1. I have a master list of emails in several columns of worksheet2. I'm trying to figure out a macro to delete emails from worksheet1 that appear in worksheet2 - basically I wish to delete the row the email is sitting on. I found one macro that only compared one column with another but have not found anything that compares two worksheets, Is there someone that can help me out?

Thanks

**davesexcel** · 10-22-2012, 07:52 PM

Post a sample workbook.
When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

**piper23** · 10-22-2012, 07:55 PM

Here's the macro that compared column A of worksheet1 with column A of worksheet2. If only I could amend this to compare to all columns of worksheet2.

Sub DeleteEmails()
    Dim rList As Range
    Dim rCrit As Range
     
    With Worksheets("Sheet1")
        Set rList = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With
    With Worksheets("Sheet2")
        Set rCrit = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With
     
    rList.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    rList.Offset(1).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
    Worksheets("Sheet1").ShowAllData
     
    Set rList = Nothing
    Set rCrit = Nothing
End Sub

**piper23** · 10-22-2012, 07:56 PM

Oops. Missed your reply. I'll send a workbook shortly.

**piper23** · 10-22-2012, 08:17 PM

Hi again.

I've attached a workbook. There are 40 emails in column A of sheet1 and 24 emails in the various columns of sheet2. There are 16 emails on sheet1 that do not appear in sheet2.

In the real world, of course, the emails will be from all manner of domains.

Thanks