Insert a row at a specific cell value

**ryan12** · 09-08-2010, 08:49 PM

I was wondering if anyone knows how to program the following macro.

I need a macro to insert an entire row when it finds a specified value in a column. To make matters more complicated it needs to copy the format from the above line onto the new line and the column will have blank cells. For example:

The Data may be:
I specify that I want to insert a line when it finds "gg"

aa
bb
cc

dd
ee
ff
gg

So the result would be:

aa
bb
cc

dd
ee
ff

gg

Thanks and feel free to ask me more questions if you are vague on anything

**Paul** · 09-08-2010, 08:59 PM

Hi Ryan, hopefully this code will work for you. Change your search range as needed, and if you don't want to hardcode the search term ("gg") you could always set it to look at a variable or even cell value.

Sub Ryan()
Dim rng As Range
Set rng = Range("A1:A20").Find("gg", [A1], xlValues, xlWhole, xlByColumns, xlNext, False)
rng.EntireRow.Insert Shift:=xlDown
End Sub

Although I suppose it could be done on one line without the rng variable..

Range("A1:A20").Find("gg", [A1], xlValues, xlWhole, xlByColumns, xlNext, False).EntireRow.Insert Shift:=xlDown

**ryan12** · 09-08-2010, 09:07 PM

Hey Paul, Thanks for your help. How do I set it to look for a cell value. In the example I gave you I used "gg" however I want this to be dynamic so it could be any value I specify, not just gg.

**Paul** · 09-08-2010, 09:11 PM

Change "gg" to a Range reference, like shown in red below (hopefully):

Range("A1:A20").Find(Range("C8").Value, [A1], xlValues, xlWhole, xlByColumns, xlNext, False).EntireRow.Insert Shift:=xlDown

If you don't see the red font, all I did was replace

"gg"

with

Range("C8").Value

Change C8 to any cell you want.

**ryan12** · 09-09-2010, 12:36 PM

Paul..thank you so much this really helps. Do you know if there is any way to copy the formulas and formats from the above row onto the newly inserted row?

**Paul** · 09-09-2010, 01:21 PM

You'd probably want to go back to the first code option then, so you can reference the range of the found item, e.g.

Sub Ryan2()
Dim rng As Range
Set rng = Range("A1:A20").Find(Range("C8").Value, [A1], xlValues, xlWhole, xlByColumns, xlNext, False)
rng.EntireRow.Insert Shift:=xlDown
rng.Offset(-2, 0).EntireRow.Copy
rng.Offset(-1, 0).EntireRow.PasteSpecial xlPasteFormulasAndNumberFormats
End Sub