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

Question

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

Hi, what you haven't told us is what is the actual issue? Is it not deleting rows.. it's deleting some rows but not all? Also, in your column B, can there ever be a situation where the string in column B has a string but doesn't has the word DELETE in it? if so, what would you want to do then? — Zac
Also, in your first If condition you have UCase("DELETE"). As you already have the string DELETE in uppercase, UCase is not needed here. Instead, have sh1.Range("B" & x).Value in uppercase (i.e. UCase(sh1.Range("B" & x).Value)). I suspect that's part of the problem as your string in column B might not always be in uppercase — Zac
I thought UCase should be in place to get the argument to ignore whether the string searched is in upper or lower case etc? The string isn't always in uppercase (could be Delete, DELETE, delete etc). I updated my question clarifying this detail — Rhyfelwr
Reading your post again, you are using InStr with vbTextCompare so comparison is not case sensitive.. bottom line you don't need UCase with this type of comparison. Change the other 2 IF's to use InStr in similar manner and implement Sam's suggestion. That should cure the issues — Zac

Sam Sam · Accepted Answer · 2019-10-08T07:52:42

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.

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

1 Answers