vba - Efficient way to delete entire row if cell doesn't contain '@'

Question

This question already has answers here:

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.