Delete row based on partial text

by

You can use AutoFilter to delete the rows which contain the text FemImplant$. This method will be much faster than looping.

If you are working with Boolean values then you may want to see Trying to Delete Rows with False Value in my Range

See this example

I am assuming that Cell A1 has header.

Sub Sample()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long
    
    strSearch = "FemImplant$"
    
    Set ws = Sheets("Sheet1")
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Remove any filters
        .AutoFilterMode = False
        
        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("A1:A" & lRow)
          .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

SNAPSHOT

enter image description here

More Related Contents:

  1. SumIf using Excel VBA
  2. Test or check if sheet exists
  3. Copy from one workbook and paste into another
  4. How to copy Outlook mail message into excel using VBA or Macros
  5. Does the VBA “And” operator evaluate the second argument when the first is false?
  6. Split cell values into multiple rows and keep other data
  7. Save each sheet in a workbook to separate CSV files
  8. Is VBA an OOP language, and does it support polymorphism?
  9. Export each sheet to a separate csv file
  10. Detect if range is empty
  11. Checking if a worksheet-based checkbox is checked
  12. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  13. Uncheck all checkboxes across entire workbook via CommandButton
  14. return csv file as recordset
  15. VBA workbooks.Close without being prompted to if the user wants to save?
  16. Extract Data from Word Document to an Excel SpreadSheet
  17. VBA equivalent to Excel’s mod function
  18. Copy a selected range to another worksheet
  19. Excel macro – paste only non empty cells from one sheet to another
  20. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  21. Pointers needed for speeding up nested loop macro in VBA
  22. ‘1004’: “The sort reference is not valid.”
  23. Select entire column in table using Excel VBA
  24. Create hyperlink to another sheet
  25. Reducing WithEvent declarations and subs with VBA and ActiveX
  26. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  27. Create folder path if does not exist (saving from VBA)
  28. Concatenate multiple ranges using vba
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment