Add 5 blank rows after Word

G

Guest

How do I insert 5 blanks after the word Books has been used.

I have about 6000 rows in my spreadsheet and there was supposed to be a
total cost. In col A there are lots of blank rows except where words such as
"Books" or "Total Books" or sometimes "Text Books" appear.

Where the word "Book" appears, I would like to add 5 rows below that word.
Thank you.

--Elaine
 
R

Ron de Bruin

Try this for column A

Sub test2()
Dim Rng As Range
Dim findstring As String
findstring = "Books"
Set Rng = Range("A:A").Find(What:=findstring, After:=Range("A" & Rows.Count), _
LookAt:=xlPart)
While Not Rng Is Nothing
Rng.Offset(1, 0).Resize(5, 1).EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=findstring, After:=Range("A" & Rows.Count), _
LookAt:=xlPart)
Wend
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

Similar Threads

find value in column and move row to 5 rows after the last one.......... 2
Insert Rows after word total 5
Inserting 3 new rows after the word total 6
Find Total and insert one blank row below 4
Macro to copy previous row and insert two blank rows 1
Shade rows A:J 8
Code To Insert Blank Rows 5
Divide two numbers in data subtotal function 2

Top