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

The main problem I am having is that, i am not able to make it loop from the bottom. The way i have it set up is looping from the top and it is not deleting consecutive rows. — user1452091
– user1452091, Commented Jul 3, 2012 at 19:03

Siddharth Rout · Accepted Answer · 2020-09-29 05:41:28Z

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

Collectives™ on Stack Overflow

Delete row based on partial text

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related