# How to Duplicate Rows in Excel

This is a discussion on How to Duplicate Rows in Excel within the Excel Questions forums, part of the Question Forums category; i have a spreadsheet of 1200 rows of data and i need to duplicate these rows 5 times each. is ...

1. ## How to Duplicate Rows in Excel

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:

1 A
2 B
3 C

1 A
1 A
1 A
2 B
2 B
2 B
3 C
3 C
3 C

thanks for any help!!

2. ## Re: How to Duplicate Rows in Excel

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!

3. ## Re: How to Duplicate Rows in Excel

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

4. ## Re: How to Duplicate Rows in Excel

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)
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub```

5. ## Re: How to Duplicate Rows in Excel

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

6. ## Re: How to Duplicate Rows in Excel

great, this worked!! the loop was super useful, thanks all for your help!

7. ## Re: How to Duplicate Rows in Excel

Originally Posted by jbeaucaire
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)
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!

8. ## Re: How to Duplicate Rows in Excel

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.

9. ## Re: How to Duplicate Rows in Excel

Hi everybody,

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

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

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 !

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•