Need Excel macro to remove invalid email addresses from spreadsheet

Tom F
Tom F used Ask the Experts™
on
I have a basic contact spreadsheet with a field for email address.  A lot of the times cell phone or fax phone numbers have erroneously been placed in this field.

What I'd like is a macro that will run thru the sheet, detect that an '@' symbol is not present in the email field and delete/remove that record entirely.

Thanks in advance,
_Draw
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
TracyVBA Developer

Commented:
Do you want to delete the entire row or do you want to delete the cell contents without the @ symbol?
Tom FI.T. and Support Staff Manager

Author

Commented:
The entire row.
VBA Developer
Commented:
OK, try this:

Option Explicit

Sub RemoveInvalidEmails()

    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    For i = lastRow To 2 Step -1
        If Not (Cells(i, 2).Value) Like "*@*" Then Rows(i).Delete
    Next i

End Sub

Open in new window

Book1.xls
Tom FI.T. and Support Staff Manager

Author

Commented:
Thanks so much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial