Deleting all rows in all sheets

[Jodie]

I have a workbook with multiple sheets and I need to delete all rows in each
sheet after the total row. The total row shows "Total" in the first column
and it is in a different row in each sheet. Can anyone help?

 

[Jacob Skaria]

Hi Jodie

Try the below

Sub Macro()
Dim ws As Worksheet, lngLastRow As Long
For Each ws In Worksheets
lngLastRow = ws.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
Set varFound = ws.Columns(1).Find("Total", , xlValues, 1)
If Not varFound Is Nothing Then
ws.Rows(varFound.Row + 1 & ":" & lngLastRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Jodie]

It worked, thank you very much.
--
Jodie

Hi Jodie

Try the below

Sub Macro()
Dim ws As Worksheet, lngLastRow As Long
For Each ws In Worksheets
lngLastRow = ws.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
Set varFound = ws.Columns(1).Find("Total", , xlValues, 1)
If Not varFound Is Nothing Then
ws.Rows(varFound.Row + 1 & ":" & lngLastRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteBelowTotalRow()
On Error Resume Next
Intersect(Range("A" & (Columns("A").Find("Total").Row + 1) & ":A" & _
Rows.Count), ActiveSheet.UsedRange).EntireRow.Delete
End Sub

 

[Rick Rothstein]

Whoops... you said "all sheets" didn't you?

Sub DeleteBelowTotalRow()
Dim WS As Worksheet
On Error Resume Next
For Each WS In Worksheets
Intersect(WS.Range("A" & (WS.Columns("A").Find("Total").Row + 1) & _
":A" & WS.Rows.Count), WS.UsedRange).EntireRow.Delete
Next
End Sub