Delete row based on partial text

Question

I am trying to delete the entire row if I find the text 'FemImplant' in column A.

The text is part of a sentence linked by '$'. I need to parse the cell content before '$' and see if it matches 'FemImplant' and delete that row.

This is what I have so far.

Dim cell As Excel.Range
RowCount = DataSheet.UsedRange.Rows.Count
Set col = DataSheet.Range("A1:A" & RowCount)
Dim SheetName As String
Dim ParsedCell() As String

For Each cell In col
    ParsedCell = cell.Value.Split("$")
    SheetName = ParsedCell(0)

    If SheetName = "FemImplant" Then
        cell.EntireRow.Delete Shift:=xlUp
    End If
Next

Answer 1

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