Hello jspinx,
This will remove the emails on the Master list that appear on the Do Not Email list. You can change the list location and starting cells (marked in red) to match what you are using.
Sub CleanEmailList()
Dim Cell As Range
Dim DoNotEmail As Object
Dim Key As String
Dim Master As Range
Dim NoList As Range
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
'Define the location and start of each list
Set Master = Worksheets("Sheet1").Range("A2")
Set NoList = Worksheets("Sheet2").Range("A2")
Set DoNotEmail = CreateObject("Scripting.Dictionary")
DoNotEmail.CompareMode = vbTextCompare
'Find the range length of the list of addresses not to email
Set Rng = NoList
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
'Load the list of addresses not to email
For Each Cell In Rng
Key = Trim(Cell.Text)
If Key <> "" And DoNotEmail.Exists(Key) = False Then
DoNotEmail.Add Key, 1
End If
Next Cell
'Find the range legnth of Master List
Set Rng = Master
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
For R = Rng.Rows.Count To 1 Step -1
Key = Trim(Rng.Cells(R, 1).Text)
If Key <> "" And DoNotEmail.Exists(Key) = True Then
Rng.Rows(R).EntireRow.Delete
End If
Next R
'Release object and free memory
Set DoNotEmail = Nothing
End Sub
Bookmarks