Delete an entire row if the content is found

Question

I want to delete an entire row if the cell starts with the text "Joe Smith" or "Jim Bob". This is the code I have from another question but need to modify:

Sub SimpleDeletingMechanism()

    Dim c As Range

    ' loop through all cells in range A1:A + last userd Row in column A
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

        ' when Joe Smith is found in cell's content
        If InStr(1, c.Text, "Joe Smith", vbTextCompare) > 0 Then

            ' delete that row


        ' when Jim Bob is found in cell's content
        ElseIf InStr(1, c, "Jim Bob", vbTextCompare) > 0 Then

            ' delete that row

        End If
    Next

End Sub

Can anyone help me fill in the gap for deleting the row if one of the names is found please?

do you have a header row? if you do I'd use an autofilter rather than looping — JosieP, Dec 17 '13 at 15:49

score 4 · Accepted Answer · answered Dec 17 '13 at 15:49

Whenever you are deleting objects from a collection, you have to iterate backwards, otherwise, as the collection is re-indexed, you end up "skipping" some of the elements.

Sub SimpleDeletingMechanism()
    Dim rng as Range
    Dim c As Range
    Dim i as Long 

    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For i = rng.Rows.Count to 1 Step -1 '// Iterate BACKWARDS over the collection
        Set c = rng.Cells(i,1)
        ' when Joe Smith is found in cell's content
        If InStr(1, c.Text, "Joe Smith", vbTextCompare) > 0 Then

            c.EntireRow.Delete


        ' when Jim Bob is found in cell's content
        ElseIf InStr(1, c, "Jim Bob", vbTextCompare) > 0 Then

            c.EntireRow.Delete

        End If
    Next

End Sub

+1 for explaining the backward iteration logic – Dec 17 '13 at 16:00 — , Dec 17 '13 at 16:00
+1 for pointing out the "skipping" – sancho.s ReinstateMonicaCellio Dec 17 '13 at 16:04 — sancho.s ReinstateMonicaCellio, Dec 17 '13 at 16:04

Delete an entire row if the content is found

1 Answers1