Jim,
While not perfect, the following code is working for me.
Assumes Column A is the column with the numbers.
Please note that you will be using close to the maximum number
of rows in a spread sheet.
'------------------------------------------------------
Sub MakeBigList()
Dim lngNum As Long
Dim lngFirstValue As Long
Dim lngColumnsWide As Long
Dim rngNewRng As Excel.Range
Dim rngCell As Excel.Range
Dim rngList As Excel.Range
Application.ScreenUpdating = False
lngColumnsWide = ActiveSheet.UsedRange.Columns.Count
Set rngList = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For lngNum = rngList.Count To 1 Step -1
Set rngCell = Cells(lngNum, 1)
lngFirstValue = Val(rngCell.Value)
If lngFirstValue > 0 Then
Set rngNewRng = rngCell(2, 1).Resize(lngFirstValue, lngColumnsWide)
rngNewRng.Insert shift:=xlShiftDown
Range(rngCell(2, 1), Cells(lngNum + lngFirstValue, _
lngColumnsWide)).Value = rngCell.Resize(1, lngColumnsWide).Value
ActiveSheet.DisplayPageBreaks = False
End If
Next 'lngNum
Application.ScreenUpdating = True
Set rngNewRng = Nothing
Set rngCell = Nothing
Set rngList = Nothing
End Sub
'------------------------------------------
Regards,
Jim Cone
San Francisco, USA
I have a set of 1000 rows. The first cell in each row contains 4,6,8,10,or 0.
I need to duplicate each row as many times as the number in the first column.
As validated by the results of the past hour, I don't have a clue how to start.
Please help
Jim
