Text Functions‎ > ‎

Duplicate Rows

                                          Sample File:   DuplicateRows.xls
"I have a set of data with unique rows of data.  I would like to duplicate each row a unique number of times. 
The number of rows needed would be found in one of the cells on each row. 
I need a macro to create these random row duplicates."
  1. Data starts in row2
  2. The column with the "number" in it indicating how many of each row is needed is the last column


Option Explicit

Sub DuplicateRows()
'Author:  Jerry Beaucaire (4/12/2012)
'duplicates each row of data resulting in a number rows
'equal to the number found in the last column of the data
Dim LR As Long, Rw As Long, Col As Long, Dupes As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
Col = Cells(1, Columns.Count).End(xlToLeft).Column

For Rw = LR To 2 Step -1
    Dupes = Cells(Rw, Col).Value - 1
    If Dupes > 0 Then
        Rows(Rw + 1).Resize(Dupes).Insert xlShiftDown
        Rows(Rw).Copy Range("A" & Rw + 1).Resize(Dupes)
    End If
Next Rw

End Sub


Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!