Deleting all rows in all sheets

J

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?
 
J

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
 
J

Jodie

It worked, thank you very much.
--
Jodie


Jacob Skaria said:
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
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top