Delete row if cell contains the word "Warning"

[CC268]

I just want to use VBA code to delete a row if a cell within that row contains the word "Warning"

Thanks!

[davesexcel]

I have a code here.
http://www.xlorate.com/excel-vba-loo...e%20to%20Range

Edit to suite your requirements.

Sub SelectA1()
    Dim FrstRng As Range, LstRw As Long, s As String
    Dim UnionRng As Range
    Dim c As Range
    
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Set FrstRng = Range("A1:A" & LstRw)
    s = InputBox("What to Find?")
    
    For Each c In FrstRng.Cells
        If c = s Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
                'MsgBox UnionRng.Address    'remove later

            Else
                Set UnionRng = c
            End If
        End If
    Next c

    UnionRng.EntireRow.Delete
End Sub

[CC268]

Is there a way to do this without the input box...I just want it to look for the word Warning within the excel sheet and delete the row it is in.

[davesexcel]

Edited code.

Not fair that you edited your question.

I have a code here.
http://www.xlorate.com/excel-vba-loo...e%20to%20Range

Edit to suite your requirements.

Sub SelectA1()
    Dim FrstRng As Range, LstRw As Long, s As String
    Dim UnionRng As Range
    Dim c As Range
    
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Set FrstRng = Range("A1:A" & LstRw)
    s = "Warning"
    
    For Each c In FrstRng.Cells
        If c = s Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
                'MsgBox UnionRng.Address    'remove later

            Else
                Set UnionRng = c
            End If
        End If
    Next c

    UnionRng.EntireRow.Delete
End Sub

[CC268]

I got an run time error "91":

Object variable or With block variable not set.

When I press the Debug button it highlights the last line "UnionRng.EntireRow.Delete"

[davesexcel]

Okay, you get the error after you run the code a second time, because there are no more rows to delete.

[CC268]

No..unfortunately it isn't deleting anything at all.

[davesexcel]

No..unfortunately it isn't deleting anything at all.

Where are the words? the code searches column A

[davesexcel]

Here is an error trap.

Sub SelectA1()
    Dim FrstRng As Range, LstRw As Long, s As String
    Dim UnionRng As Range
    Dim c As Range

    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Set FrstRng = Range("A1:A" & LstRw)
    s = "Warning"

    For Each c In FrstRng.Cells
        If c = s Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
                'MsgBox UnionRng.Address    'remove later

            Else
                Set UnionRng = c
            End If
        End If
    Next c
    If Not UnionRng Is Nothing Then
        UnionRng.EntireRow.Delete
    End If
End Sub

[CC268]

Hmm...ran it twice...still nothing. If it matters the Warning message is in a merged cell...but I don't think that should matter.

[davesexcel]

You may have to supply a sample.

[CC268]

You may have to supply a sample.

Alright I attached the sheet...I deleted all the data but at least you can see the layout and where the Warning message is (all the way at the bottom). I know it probably just makes sense to scroll to the bottom and delete it manually, but if I can automate it that would be nice as I am doing a lot of other VBA on this sheet.

[Winon]

Hello CC268,

In your Post#1, you initially stated a different requirement, hence the suggestions of an Input Box, from the contributors to your Thread.

Contributors to this Forum spend their time and money to assist others with a specific issue related to their field of expertise, free of charge, and without expecting any recognition for their effort/s. They would be glad to at least receive some sort of comment, by also Adding to their Reputation, by clicking on the star at the bottom left of their Post/s.

In future, please take a moment to exactly determine your required issue. before posting.

Thank you for your understanding.

Regards.

[jeffreybrown]

Hello CC268,

Administrative Note:

• As per Forum Rule #12, please limit quotes to only that which is necessary -- otherwise it's just clutter...Thanks.

[jolivanes]

In post #1 you suggest that the word "Warning" could be anywhere in a row.
In your example there is no cell with the word "Warning". There is "Warning" as part of a whole lot more in one cell

If the cell you want to delete is always at the bottom, just clear or delete the last row or cell

Cells(RowsCells(.Count, 1).End(xlUp).ClearContents

or

Rows.Count, 1).End(xlUp).EntireRow.ClearContents

Or you could change Dave's code to

Sub SelectA2()
    Dim FrstRng As Range, LstRw As Long, s As String
    Dim UnionRng As Range
    Dim c As Range

    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Set FrstRng = Range("A1:A" & LstRw)
    s = "WARNING"

    For Each c In FrstRng.Cells
        If Left(c, 7) = s Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
                'MsgBox UnionRng.Address    'remove later

            Else
                Set UnionRng = c
            End If
        End If
    Next c
    If Not UnionRng Is Nothing Then
        UnionRng.EntireRow.ClearContents    '<----- or delete
    End If
End Sub

Also note search words spellings. Capital letters or small letters.

[davesexcel]

It looks like you will just have the word once, you can find that word and delete the row

Sub Button1_Click()

    Dim ws As Worksheet
    Dim c As Range, FindS As String

    Set ws = Worksheets(1)

    FindS = "*Warning*"

    With ws
        Set c = .Cells.Find(what:=FindS, lookat:=xlWhole)

        If Not c Is Nothing Then
            c.EntireRow.Delete
        Else: MsgBox "Not Found"
            Exit Sub
        End If
    End With

End Sub

I am going to assume you need to delete that row in a bunch of worksheets.

Sub Loop_Sheets()

    Dim ws As Worksheet
    Dim c As Range, FindS As String

    FindS = "*Warning*"

    For Each ws In Sheets

        With ws

            Set c = .Cells.Find(what:=FindS, lookat:=xlWhole)

            If Not c Is Nothing Then
                c.EntireRow.Delete
            Else:    'MsgBox "Not Found"
                'Exit Sub
            End If

        End With

    Next ws

End Sub