MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto-insert rows

Posted by Stephen Chacon on May 22, 2001 10:51 AM

Can someone help me? Ive got an Excel spreadsheet with subtotals in it. What I would like to be able to do is insert rows before and after each subtotal through a macro.

Thank You

Posted by Dave Hawley on May 22, 2001 2:16 PM

Hi Stephen

Try this code:

Sub InsertRows()
Dim i As Integer
Dim rRw As Range
'Written by OzGrid Business Applications
Set rRw = Range("A1")

For i = 1 To WorksheetFunction.CountIf(Columns(1), "* Total")
Set rRw = Columns(1).Find(What:="* Total", After:=rRw.Offset(2, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

If rRw.Value <> "Grand Total" Then
rRw.Offset(1, 0).EntireRow.Insert
End If
Next i

Set rRw = Nothing

End Sub


OzGrid Business Applications