Results 1 to 9 of 9

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. #1
    New Member
    Join Date
    Jan 2010
    Posts
    2

    Unhappy 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:

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

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Location
    Still in Kansas
    Posts
    367

    Default 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!
    Work - 2003 & 2007. Home - 2007.

  3. #3
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Location
    San Antonio, Texas
    Posts
    4,549

    Default 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
    Jeff

  4. #4
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,919

    Default 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)
    Range("A1").CurrentRegion.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    End Sub
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  5. #5
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Location
    San Antonio, Texas
    Posts
    4,549

    Default 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
    Jeff

  6. #6
    New Member
    Join Date
    Jan 2010
    Posts
    2

    Default Re: How to Duplicate Rows in Excel

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

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    2

    Default Re: How to Duplicate Rows in Excel

    Quote Originally Posted by jbeaucaire View Post
    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!

  8. #8
    New Member
    Join Date
    Jul 2014
    Posts
    2

    Default 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. #9
    New Member
    Join Date
    Jun 2015
    Posts
    2

    Default 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:

    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 !

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com