Efficient way to delete entire row if cell doesn't contain '@' [duplicate]

Question

I'm creating a fast sub to do a validity check for emails. I want to delete entire rows of contact data that do not contain a '@' in the 'E' Column. I used the below macro, but it operates too slowly because Excel moves all the rows after deleting.

I've tried another technique like this: set rng = union(rng,c.EntireRow), and afterwards deleting the entire range, but I couldn't prevent error messages.

I've also experimented with just adding each row to a selection, and after everything was selected (as in ctrl+select), subsequently deleting it, but I could not find the appropriate syntax for that.

Any ideas?

Sub Deleteit()
    Application.ScreenUpdating = False

    Dim pos As Integer
    Dim c As Range

    For Each c In Range("E:E")

        pos = InStr(c.Value, "@")
        If pos = 0 Then
            c.EntireRow.Delete
        End If
    Next

    Application.ScreenUpdating = True
End Sub

Answer 1

You don't need a loop to do this. An autofilter is much more efficient. (similar to cursor vs. where clause in SQL)

Autofilter all rows that don't contain "@" and then delete them like this:

Sub KeepOnlyAtSymbolRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("E1:E" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*@*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

NOTES:

• .Offset(1,0) prevents us from deleting the title row
• .SpecialCells(xlCellTypeVisible) specifies the rows that remain after the autofilter has been applied
• .EntireRow.Delete deletes all visible rows except for the title row

Step through the code and you can see what each line does. Use F8 in the VBA Editor.

Answer 2

Have you tried a simple auto filter using "@" as the criteria then use

specialcells(xlcelltypevisible).entirerow.delete

note: there are asterisks before and after the @ but I don't know how to stop them being parsed out!