Excel Search & Replace -> delete all listed keywords (a list) from workbook

[Biedubbeljoe]

Hi,
I have a list of emails (different every week) that needs to be matched against a workbook. All emails on that list needs to be deleted in the workbook. I recorded a Excel Macro but it needs adjustment.
The first email on column J (J1) needs to be processed, then the next etc. till the last email from the list. Only the email address from the J column list are lined up for deleting all matching emails in the workbook. Can somebody please adjust my code below:
Sub Macro1()
'
' Macro1 Macro
'

'
Range("J2").Select
Selection.Cut
Cells.Replace What:="1@email.com", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Application.CutCopyMode = False
Range("J3").Select
Selection.Cut
Cells.Replace What:="2@email.com", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Application.CutCopyMode = False
Range("J4").Select
Selection.Cut
Cells.Replace What:="3@email.com", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("J4").Select
Application.CutCopyMode = False
Range("J5").Select
Selection.Cut
Cells.Replace What:="4@email.com", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("J5").Select
Application.CutCopyMode = False
Range("J6").Select
End Sub

 

[Joe4]

Can you clarify a few things for us?

So, are the values you want to replace/remove are in column J on the current (active) sheet?

What range do you want to remove these values from?
Is it the same sheet, or a different one?
What columns/rows are to be included in this removal?

 

[Biedubbeljoe]

Thanks for the quick reply Joe4.
The range where I like to remove the values from are all Columns in the whole workbook.
The values are in Column J (but could be placed in any empty Column available).
Cheers.

 

[Joe4]

The range where I like to remove the values from are all Columns in the whole workbook.
The values are in Column J (but could be placed in any empty Column available).

It is best NOT to store the values you want to replace in the same sheet you want to replace (or else you will find every single item in column J)!
If you want to search the entire sheet, it would be best to store the values you want to replace/remove in a separate sheet in the workbook.

 

[Biedubbeljoe]

I am sorry, maybe I didn't explain the case properly. As an example, I have a list of 50 email addresses and a workbook with multiple sheets containing 2000 email addresses in different sheets, also within text.
I like to delete all email addresses in all sheets of the workbook based upon my list of 50 email addresses. All other email addresses must stay.

I did my best for a new macro, based upon my recording, but I get an error and do not know if this is the proper way to handle this:
Sub LoopThrough()
'
' Delete emails based on list in column J,
' List range from 1 t/m 500

'

For Each Column In Range("J1:J500").Columns
For Each Cell In ActiveWorkbook.Worksheets
Cell.Copy
Cells.Replace What:=Cell.Value, Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Application.CutCopyMode = False
Next
Next
End Sub

I know that What:=Cell.value is causing the problem, but do not know how to get the stored value being replaced by "".
Any help would be appreciated.

 

[Joe4]

OK, so you did not mention that you are trying to do this on multiple sheets within your workbook.
Is it safe to assume that you have this list of values to replace in column J on some certain worksheet, and you want to do the removals on ALL the OTHER sheets?
If so, what is the name of the sheet with the column J of the values you want to remove?

 

[Biedubbeljoe]

Name of the sheet is "remove"
I presume based on your answer, that it is preferable to put the 50 email address values in a separate empty sheet of the workbook?
I have tried so many code snippets without success. Thanks for your efforts Joe4.

 

[Joe4]

Try this:

Sub RemoveEmails()

    Dim ws As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim eml As String
    Dim w As Worksheet
    
'   Set sheet where values to remove are found
    Set ws = Sheets("remove")
    
'   Find last cell in column J on "remove" sheet with value
    lr = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
'   Loop through each value in column J on remove sheet
    For r = 2 To lr
'       Get value to look for
        eml = ws.Cells(r, "J")
'       Loop through each sheet
        For Each w In ActiveWorkbook.Worksheets
'           Skip remove sheet
            If w.Name <> ws.Name Then
'               Replace values
                w.Cells.Replace What:=eml, Replacement:="", LookAt:= _
                    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            End If
        Next w
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[Biedubbeljoe]

Fantastic Joe4, thanks a million. It works

 

[Joe4]

You are welcome.
Glad I was able to help!