Results 1 to 6 of 6

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

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

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

    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!

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