Adding duplicate rows & blank rows

[Lorn]

my problem is i'm trying to edit a section of text for some software but im trying to do it a quick way via excel
The text is over a thousand lines one word in each line kinda like this

text1
text2
text3
text4

I'm wanting to duplicate the text and ad 11 spaces between each section like so..

text1
text1










text2
text2










text3
text3

But I'm looking for either a formula for excel, so that I can highlight the whole document and apply the "duplicate and spacing" in one swoop, rather than doing each individual.

Help me please!!

 

[Domenic]

It looks like there's 10 blank rows between each section. Assuming that A2:A5 contains the data, try...

B2, copied down:

=IF(ISNUMBER(MATCH(MOD(ROWS(B$2:B2)-1,12)+1,{1,2},0)),INDEX($A$2:$A$5,INT((ROWS(B$2:B2)-1)/12)+1),"")

 

[Datsmart]

This code will do what you are asking.

Sub InsertExtraRows()
Dim i As Long, LstRw As Long
    LstRw = [A65536].End(xlUp).Row
    For i = LstRw To 2 Step -1
        Cells(i + 1, 1).Value = Cells(i, 1).Value
        Range(Cells(i, 1), Cells(i + 11, 1)).EntireRow.Insert
        If i = 2 Then
            Cells(i, 1).Value = Cells(1, 1).Value
        End If
    Next i
End Sub

 

[Lorn]

Hi Dominic,

Very clever!

There are 11 blank rows... can you redo the formula please??

thanks

Lorn

 

[Domenic]

Try...

=IF(ISNUMBER(MATCH(MOD(ROWS(B$2:B2)-1,13)+1,{1,2},0)),INDEX($A$2:$A$5,INT((ROWS(B$2:B2)-1)/13)+1),"")

 

[Lorn]

Thanks dominic, i figured that out as soon as I'd posted that!

Thanks again you whiz!

Lorn