Insert rows beneath cells containing specific text

**adame** · 07-22-2011, 05:38 AM

Hi,

I am trying to format a large number of tables for pasting into a Word doc.

Is there an easy way to insert a row on a worksheet beneath any cell containing specific text - in this case, 'market summary'.

Many thanks,

Adam

**adame** · 07-22-2011, 07:01 AM

I figured it out if anyone is interested!

Sub Insert_Rows()
Rw = 2
LastRow = 525
NxtChk:
If Cells(Rw, "f") = "Market summary" Then
Rows(Rw + 1).EntireRow.Insert
Rw = Rw + 1
NewRow = NewRow + 1
End If
If Rw = LastRow + NewRow Then Exit Sub
Rw = Rw + 1
GoTo NxtChk
End Sub

**martindwilson** · 07-22-2011, 07:07 AM

number your rows down from 1 to end
say your test is in column a2:a1001 so 1 to 1000 in say col x x2:x1001
after thest entry in x in this case x1002 put =if(a2="market summary",x2,"") drag down to row 2002
now sort by column x
if cell contains more text than just markrt summart
use
=if(isnumber(SEARCH("market summary",A2)),x2,"")
see small example using col b
just sort by sort colum b then delete col b