Delete rows that do not contain an email address

**Davbuf** · 12-21-2012, 03:56 PM

Have a sheet with colunms First, Last, Address, City, State, Zip, Email. Some cells in the Email column contain a random value i.e. Z9A1BNSZ.

Looking for a Macro that will delete all rows (records) that do not contain proper email addresses i.e. email@email.com.

Any help would be appreciated.

Thanks.

Davbuf

**aelgadi** · 12-21-2012, 04:10 PM

Tri this

Public Sub Test()
    Dim eRange As Range
    Dim Bool As Boolean
    Dim i As Long
    Bool = True
    With Sheet1
        For i = 1 To .Rows.Count
            If InStr(1, .Cells(i, "A").Value, "@") > 2 Then
                If Bool = True Then
                    Bool = False
                    Set eRange = Rows(i)
                Else
                    Set eRange = Union(eRange, Rows(i))
                End If
            End If
        Next i
        eRange.EntireRow.Delete
    End With
End Sub

**Davbuf** · 12-21-2012, 04:28 PM

Thanks aelgadi. I'm getting this error:

Run-time error '91':
Object variable or With block variable not set

**aelgadi** · 12-21-2012, 05:05 PM

that means didn't find any cell string that have "@". Have you adjusted range in the above macro cause mine assuming Column "A"

**Davbuf** · 12-21-2012, 05:12 PM

Ok aelgadi. The email addressess are in Column G. I made the adjustment however the result was that the email addresses in column G simply lost all characters after the @. For example email1@email.com turns into email1. Random values still remain (BPX2BFFU).

**aelgadi** · 12-21-2012, 05:09 PM

This explains it

Public Sub Test()
    Dim eRange As Range
    Dim Bool As Boolean
    Dim NotFound As Boolean
    Dim i As Long
    Bool = True
    NotFound = False
    With Sheet1
        For i = 1 To .Rows.Count
            If InStr(1, .Cells(i, "A").Value, "@") > 2 Then
                If Bool = True Then
                    Bool = False
                    Set eRange = Rows(i)
                    NotFound = True
                Else
                    Set eRange = Union(eRange, Rows(i))
                    NotFound = True
                End If
            End If
        Next i
        If NotFound = True Then
            eRange.EntireRow.Delete
        Else
            MsgBox "No e-mail found."
        End If
    End With
End Sub

**AB33** · 12-21-2012, 04:58 PM

Assuming the data you wish to delete is in column A

Sub InsertRows()
Dim lr As Long, i As Long

    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = lr To 2 Step -1
        If InStr(Cells(i, "A"), "@") = 0 Then Rows(i).delete
    Next i
    
    Application.ScreenUpdating = True

End Sub

**Davbuf** · 12-21-2012, 05:18 PM

That worked AB33. Thanks! Thank you as well aelgadi.

**AB33** · 12-21-2012, 05:34 PM

You are welcome!

Delete rows that do not contain an email address

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Re: Delete rows that do not contain an email address

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions