CODEWITHSUNDEEP
excelvba
Rhyfelwr
Rhyfelwr

Reputation: 329

Delete rows containing a specific string while keeping rows where the cell is blank

I have a worksheet where some cells in column "B" contain strings (they all have "DELETE" in them in some form) while some cells are blank.

I want to delete all the rows where the cell in column "B" contains the string "DELETE" but keeping the rows where the cell in column "B" is blank.

I can only determine the range (lastrow) of the loop based on column "A" as it is filled down to the end.

Another thing I try to do in my loop is delete all rows where a cell in column "E" doesn't equal to the string "Active". This doesn't work.

The string "DELETE" could be upper or lower case randomly ("DELETE", "delete", "Delete" etc.)

My code deletes a few rows then stops.

Sub format_pull()
Dim sh1 As Worksheet

Set sh1 = Sheets("Sheet1")

    LastRow = crc.Cells(Rows.Count, "A").End(xlUp).Row
    For x = 2 To LastRow

        If InStr(1, sh1.Range("B" & x).Value, UCase("DELETE"), 1) = 0 Then sh1.Range("B" & x).Value = "KEEP"
        If sh1.Range("B" & x).Value = "DELETE" Then sh1.Range("B" & x).EntireRow.Delete
        If sh1.Range("E" & x).Value <> "Active" Then sh1.Range("E" & x).EntireRow.Delete

    Next x

End Sub

Upvotes: 0

Views: 265

Answers (1)

Sam
Sam

Reputation: 5721

When deleting inside a loop, it is always better to do it backwards, like this:

For x = LastRow To 2 Step -1
    ...
Next x

That way it won't mess up when something gets deleted, but the row counter doesn't adjust.

Upvotes: 3

Related Questions