How to remove all emails with certain domains from the list in one go?

[LizzieLiz]

How do I remove all emails with certain domains (e.g. gmail, hotmail, yahoo) in one go?
I've got a list where a couple of emails are from the same company. I want to remove all emails with particular domains (around 100+ of them).
Is there a way to do that in one go instead of multiplying same effort x100, eg. Find&Replace x 100?

I tried to be as clear as possible

Thank you!

 

[Z51]

Let's assume your list of domains to clear are in A1:A100, and your list of e-mail addresses is in column F, e.g. F1:F5000. This code will put the bad domains into an array and then check each e-mail address in the list to see if it finds a match. If so, it clears that cell value. You can have it do other things like delete the entire row, etc. (In that case, your list of domains shouldn't overlap rows, preferably put it on another sheet.)

Sub LizzieLiz()
Dim myList As Variant, i As Long, lastrow As Long, j As Long
lastrow = Range("F" & Rows.Count).End(xlUp).Row
myList = Sheets("Sheet1").Range("A1:A100").Value

For i = 1 To lastrow
    For j = LBound(myList) To UBound(myList)
        If Cells(i, 6).Value Like "*" & myList(j, 1) & "*" Then Cells(i, 6).ClearContents
    Next j
Next i
End Sub

 

[LizzieLiz]

Let's assume your list of domains to clear are in A1:A100, and your list of e-mail addresses is in column F, e.g. F1:F5000. This code will put the bad domains into an array and then check each e-mail address in the list to see if it finds a match. If so, it clears that cell value. You can have it do other things like delete the entire row, etc. (In that case, your list of domains shouldn't overlap rows, preferably put it on another sheet.)

Sub LizzieLiz()
Dim myList As Variant, i As Long, lastrow As Long, j As Long
lastrow = Range("F" & Rows.Count).End(xlUp).Row
myList = Sheets("Sheet1").Range("A1:A100").Value

For i = 1 To lastrow
    For j = LBound(myList) To UBound(myList)
        If Cells(i, 6).Value Like "*" & myList(j, 1) & "*" Then Cells(i, 6).ClearContents
    Next j
Next i
End Sub

Im getting runtime error 1004 in the third line.

 

[Z51]

Try specifying the sheet name, e.g.

lastrow = Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row

Also, in the "If Cells(i, 6)..." line, add the sheet references:

If Sheets("Sheet1").Cells(i, 6).Value Like "*" & myList(j, 1) & "*" Then Sheets("Sheet1").Cells(i, 6).ClearContents