How to Duplicate Rows in Excel

[hayhackey]

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!!

 

[dscg]

This macro should automate this for you nicely:

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!

 

[jeffreybrown]

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

 

[jbeaucaire]

Without a loop:

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

 

[jeffreybrown]

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

 

[hayhackey]

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

 

[Czakalli]

Without a loop:

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!

 

[Czakalli]

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:

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.

 

[rid95]

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 !