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

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

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

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" <(E-Mail Removed)> wrote in message
news:59F965FD-D2A8-4EDB-816F-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:59F965FD-D2A8-4EDB-816F-(E-Mail Removed)...
>>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
>