Inserting 3 new rows after the word total

[dd]

Hi, I'm working with multiple rows of data, probably around 3000 rows
and I have the word "total" in between the data in Column A in Excel. I

want to add three blank rows after the word Total. The word Total is
also in bold font. Can someone please help with a macro? Thanks.

 

[Bob Phillips]

see response in public.excel

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

dd, I think this will do your job, assuming the items are in column A.

Sub insert3rows()

Dim i As Integer
Dim rCell As Range
Dim lFirstRow As Long
With ActiveSheet.Range("A:A")
Set rCell = .Find("total", _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rCell Is Nothing Then
lFirstRow = rCell.Row
Do
With rCell.Offset(1, 0)
For i = 1 To 3
.Insert xlShiftDown
Next i
End With
Set rCell = .FindNext(rCell)
Loop While Not rCell Is Nothing And rCell.Row <> lFirstRow
End If
End With

End Sub

 

[dd]

Thanks Martin.
I'll test this next week but it seems to work fine for now.
I also rated this 5 stars.
Thanks again.

 

[dick]

Thanks Martin.
I'll test this next week but it seems to work fine for now.
I also rated this 5 stars.
Thanks again.

 

[dick]

I found this solution helpful but have been trying to conditionally
format a worksheet based on values in different cells.
The thing I haven't been able to do is insert a row above cells of a
given value without getting into a long/infinite loop.
A solution similar to the one below but inserting above the cell would
be brilliant - can anyone help pls.

 

[dd]

Hi,
I test this today and it did not work on my data. Probably because I
used the Data->Subtotal function in Excel to get the total. This macro
did not insert any rows. But when I opened a new worksheet and manually
typed in a few rows of data in Col A with the word total, it did insert
3 new rows after total. How would I insert 3 new rows after using the
Subtotal function? Thanks