How to delete a row based on a condition

[rbysetty]

I was hoping someone could help with this piece of code. I wam trying to delete the entire row if I find the text 'FemImplant' in column A. The tricky part is that this text is part of a sentence linked by '$'. So I need the code to parse out the cell content before '$' and see if it matches 'FemImplant' and delete that row. This is what I have so far but it is nto working.

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

 

[Domski]

Try:

Sub Test()
    With Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="=*FemImplant*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Dom

 

[Domski]

If you want to delete rows by looping through them you need to do so from the last row to the first otherwise you will skip consecutive rows that match the criteria.

Dom

 

[rbysetty]

If you want to delete rows by looping through them you need to do so from the last row to the first otherwise you will skip consecutive rows that match the criteria.

Dom

How do I acheive this, I tried excel help but was of no use, could you please point me the right direction ?
how do i loop from the bottom ?

 

[Domski]

Like this:

Sub test()

    Dim lngLoopRow As Long


    For lngLoopRow = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & lngLoopRow) Like "*FemImplant*" Then
            Rows(lngLoopRow).Delete
        End If
    Next lngLoopRow


End Sub

Dom

 

[rbysetty]

I had to make a little but the idea worked. thanks !