Hello jspinx,
Are these lists on the same worksheet? If so, do the lists share any rows?
Hello jspinx,
Are these lists on the same worksheet? If so, do the lists share any rows?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hello Leith
they are on different files. Each entry is a different row
Hello jspinx,
Good, that makes life much easier. I'll start coding the macro.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks