Insert row

[cdawley4]

I have a spreadsheet with about 200 rows and I want to write a script to insert 2 rows after the row containing the text. Ex:

This is what I have in excel.

abcd
abcd
abcd
abcd

This is what I want it to look like.

abcd
(skip 1 row)
(skip 1 row)
abcd
(skip 1 row)
(skip 1 row)
abcd
(skip 1 row)
(skip 1 row)
abcd
(skip 1 row)
(skip 1 row)
abcd


Any information would be helpful.

Chris

 

[jbpelletier]

this should do the trick

Dim XL As Object
Dim lin As Integer
lin = 1

Set XL = CreateObject("Excel.Application")
XL.Application.Visible = True

While XL.ActiveSheet.Cells(lin, 1) <> ""
lin = lin + 1
ActiveSheet.Rows(lin).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
lin = lin + 2
Wend

set XL=nothing


jb

 

[cdawley4]

jb,

Thanks for the reply. When I ran it the first time, it came back with an error in the debug process, however, I was able to fix it by removing

Set XL = CreateObject("Excel.Application")
XL.Application.Visible = True

and changing While XL.ActiveSheet.Cells(lin, 1) <> "" to

While ActiveSheet.Cells(lin, 1) <> ""

Thanks again,

Chris