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

by

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.

More Related Contents:

  1. Duplicate number and it value in column EXCEL [closed]
  2. Rearranging Excel Cell based on Value [closed]
  3. How do I get the old value of a changed cell in Excel VBA?
  4. Force a screen update in Excel VBA
  5. Find all matches in workbook using Excel VBA
  6. Create a new sheet for each unique agent and move all data to each sheet
  7. Set Auto Filtering multiple wildcards
  8. How to make Excel VBA variables available to multiple macros?
  9. Detect merged cells in VBA Excel with MergeArea
  10. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  11. Parse HTML content in VBA
  12. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  13. How do I name a spreadsheet automatically, by referencing two cells?
  14. Excel VBA Delete Rows
  15. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  16. Should I turn .CutCopyMode back on before exiting my sub procedure?
  17. VBA: How to delete filtered rows in Excel?
  18. Determine whether user is adding or deleting rows
  19. How to use VBA SaveAs without closing calling workbook?
  20. Removing the password from a VBA project
  21. Removing special characters VBA Excel
  22. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  23. Iterate over slicer via VBA and select a single item each time
  24. Loop through Excel Sheets
  25. Return a range from A1 to the true last used cell
  26. How to add items to a combobox in a form in excel VBA?
  27. How can I open this VBA workbook faster?
  28. How can I automate Save as dialog box in IE11 using VBA?
  29. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  30. Conditional formatting using 3 conditions in Macro/VBA

Leave a Comment