Excel 2013 deleting rows with specific text without leaving blank rows

Question

I'm trying to clarify logs where there are comments in some of the entries and in some there aren't. So what i would like to be able to do is to delete entire rows where there are comments in a column such as NULL and so that it wouldn't leave a blank row behind it because it messes up my other conditional formatting rules.

I'm guessing this kind of automation requires VBA but since i'm not a full time coder, I have no idea how to even begin solving this.

Thank you for all responses in advance!

score 0 · Accepted Answer · answered May 24 '18 at 07:24

This isn't a very sophisticated answer, but neither is your question so hopefully it'll get you going. This assumes your evaluating is in column A. You can obviously change that. If you've got tens of thousands of rows, you might want to turn off some functions like screen updating, but that's a different type of question.

Sub ClearThatOut()
Dim rCell As Range

restart:

For Each rCell In Intersect(Columns("A"), ActiveSheet.UsedRange).Cells
    If UCase(rCell.Value2) = "NULL" Then
        rCell.EntireRow.Delete
        GoTo restart
    End If

    'Example of secondary text, you could put as many of these as needed.
    If UCase(rCell.Value2) = "OH NO!" Then
        rCell.EntireRow.Delete
        GoTo restart
    End If

Next rCell

End Sub

You're proposition worked exactly as I wanted! You have my undying gratitude. Thx a bunch! — mhyvarinen, May 24 '18 at 07:51

Excel 2013 deleting rows with specific text without leaving blank rows

1 Answers1