Find a cell and delete a row

[markman235]

Hi everyone,

I have a sheet with multiple tabs, and they all contain data exept one that is the summary tab. The summary tab has a max formula to look across all tabs, and that works great.

My problem is that at the bottom of every data tab is a grand total, and this is what the formula is finding. So I would like to use a macro to find the words "Grand Total" and then delete the entire row.

Any help on this would be greatly appeciated.

Thank you!

Mark

 

[mumps]

Try:

Sub DeleteRow()
    Application.ScreenUpdating = False
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            ws.Rows(ws.Cells.Find("Grand Total", LookIn:=xlValues, lookat:=xlWhole).Row).EntireRow.Delete
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

Mumps,

Your code is much shorter than what I was going to post. However, you may want to make one small adjustment. If it does not find the phrase "Grand Total" on a sheet, it throws up an error and stops.
You might just want to add some error handling in the case in whcih it does not find that phrase on a page.
Just thinking about typical users, even if every page has Grand Totals, it is possible that the user may accidentally try to run the macro more than once.

 

[mumps]

This should do it:

Sub DeleteRow()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, GT As Range
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set GT = ws.Cells.Find("Grand Total", LookIn:=xlValues, lookat:=xlWhole)
            If Not GT Is Nothing Then
                ws.Rows(GT.Row).EntireRow.Delete
            Else
                MsgBox ("'Grand Total' not found in sheet " & ws.Name)
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[markman235]

Thank you!

I do have one minor problem, though. I get an error that says "Run-time error '91': Object variable or With block variable not set.

And it has the row ws.rows(ws.cells.find(.... highlighted in yellow.

 

[mumps]

Have you tried the macro I posted in Post #4 (as @Joe4 suggested)?

 

[markman235]

This should do it:

Sub DeleteRow()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, GT As Range
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set GT = ws.Cells.Find("Grand Total", LookIn:=xlValues, lookat:=xlWhole)
            If Not GT Is Nothing Then
                ws.Rows(GT.Row).EntireRow.Delete
            Else
                MsgBox ("'Grand Total' not found in sheet " & ws.Name)
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

This works great! Thank you!

 

[mumps]

You are very welcome. Glad we could help.