Adding duplicate rows & blank rows

Lorn

New Member
Joined
Oct 26, 2009
Messages
16
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!!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will do what you are asking.
Code:
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

New Member
Joined
Oct 26, 2009
Messages
16
Hi Dominic,

Very clever!

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

thanks

Lorn
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Oct 26, 2009
Messages
16
Thanks dominic, i figured that out as soon as I'd posted that!

Thanks again you whiz!

Lorn

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,736
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top