Deleting all rows in all sheets
 

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?
--
Thank you, Jodie

Deleting all rows in all sheets
 

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
---------------
Jacob Skaria


"Jodie" wrote:

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?
--
Thank you, Jodie

Deleting all rows in all sheets
 

It worked, thank you very much.
--
Jodie


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jodie" wrote:

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?
--
Thank you, Jodie

Deleting all rows in all sheets
 

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 (MVP - Excel)


"Jodie" wrote in message
...
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?
--
Thank you, Jodie

Deleting all rows in all sheets
 

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Jodie" wrote in message
...
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?
--
Thank you, Jodie