How to Duplicate Rows in Excel

hayhackey

New Member
Joined
Jan 26, 2010
Messages
2
i have a spreadsheet of 1200 rows of data and i need to duplicate these rows 5 times each. is there any way to easily do this and avoid manually inserting or copying/pasting all 1200 rows? all i can find is how to DELETE duplicate rows, not how to ADD them. see below for an example:

original spreadsheet:
1 A
2 B
3 C

desired spreadsheet:
1 A
1 A
1 A
2 B
2 B
2 B
3 C
3 C
3 C

thanks for any help!!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This macro should automate this for you nicely:

Code:
Sub Duper()
Dim LR As Long
Dim i As Long

LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For i = LR To 1 Step -1
        Rows(i).Copy
        Range(Rows(i + 1), Rows(i + 2)).Insert Shift:=xlDown
        Application.CutCopyMode = False
    Next i

End Sub

HTH!
 
Upvote 0
dscg,

Macro works great, but you might need to adjust

For i = LR To 1 Step -1

to

For i = LR To 2 Step -1
 
Upvote 0
Without a loop:
Code:
Option Explicit

Sub TimesFive()
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row
Dim BR As Long:     BR = LR * 5

Rows("1:" & LR).Copy Rows(LR + 1 & ":" & BR)
Range("A1").CurrentRegion.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End Sub
 
Upvote 0
That's neat JB...

dscg, sorry about that correction I was wrong. My change was due to a header row I put in which the OP did not have...Sorry
 
Upvote 0
Without a loop:
Code:
Option Explicit

Sub TimesFive()
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row
Dim BR As Long:     BR = LR * 5

Rows("1:" & LR).Copy Rows(LR + 1 & ":" & BR)
Range("A1").CurrentRegion.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End Sub

Hi,

This code works great if i don't have formulas in the fields.

Currently if the cell formula in Sheet2!C1 is =Data!A1 on the row below (Sheet2!C2) the VB code would insert =Data!A2. Putting $ sign's isn't an option as I wan't Sheet2!C3 to keep the formula: =Data!A2 and so on...

What i want to achieve is list of rows and on a second spreadsheet with each row of the orignal sheet to show eventually 5times. Changing the value on the original should change on 5 target rows.

Any ideas how to modify the VB code here?
Thanks!
 
Upvote 0
Found the solution to the question i asked above, in case anyone else will wonder:

Just needed to change the formating of the formula to Text and afterwards change it back to general or whatever needed.

+ It seems i quoted the wrong code:

Code:
Sub Duper()
Dim LR As Long
Dim i As Long

LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For i = LR To 1 Step -1
        Rows(i).Copy
        Range(Rows(i + 1), Rows(i + 4)).Insert Shift:=xlDown
        Application.CutCopyMode = False
    Next i

End Sub

Cheers.
 
Upvote 0
Hi everybody,

I am looking for a code (similar to the one in the previous message) that would enable me to do this:

Original spreadsheet:
1A
2B
3C
4D
5E
6F
7G
8H
9I
10J
11K
12L
13M
14N

DESIRED SPREADSHEET:
1A
1A
1A
1A
2B
2B
2B
2B
2B
2B
3C
3C
3C
3C
4D
4D
4D
4D
5E
5E
6F
6F
6F
6F
7G
7G
7G
7G
7G
7G
7G
7G
7G
7G
8H
8H
8H
8H
8H
8H
9I
9I
9I
9I
10J
10J
10J
10J
11K
11K
11K
11K
12L
12L
12L
12L
13M
13M
13M
13M
14N
14N
14N
14N
14N
14N
14N
14N
14N
14N
14N
14N

The idea would be to make :
-4 copies of the 1st row
-6 copies of the 2nd row
-4 copies of the 3rd row
-4 copies of the 4th row
-2 copies of the 5th row
-4 copies of the 6th row
-10 copies of the 7th row
-6 copies of the 8th row
-4 copies of the 9th row
-4 copies of the 10th row
-4 copies of the 11th row
-4 copies of the 12th row
-4 copies of the 13th row
-12 copies of the 14th row

and then in the next row I want it to restart again like with the first row and so on until the last row...

I tried to use the code proposed in the previous coment but it make the same number of copies for each row. So if anyone knows how to modify it to make a different number of copies depending on the that would be great. Thank you for your help !
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top