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
'www.ozgrid.com
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, _
MatchCase:=False)
If rRw.Value <> "Grand Total" Then
rRw.EntireRow.Insert
rRw.Offset(1, 0).EntireRow.Insert
End If
Next i
Set rRw = Nothing
End Sub
Dave
OzGrid Business Applications
