Results 1 to 8 of 8

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,385

    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,834

    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,385

    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.

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