Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicating Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicating Rows
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 "Jim Berglund" wrote in message news:ck8Yd.639045$6l.356794@pd7tw2no... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicating Rows
Well, I was right - I didn't have a clue as to how to start!
It works! And there's a lot for me to think through. Thanks, Jim Jim Berglund "Jim Cone" wrote in message ... 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 "Jim Berglund" wrote in message news:ck8Yd.639045$6l.356794@pd7tw2no... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duplicating rows | Excel Discussion (Misc queries) | |||
Formula for Duplicating Rows | Excel Discussion (Misc queries) | |||
Duplicating rows help needed | Excel Worksheet Functions | |||
Formula for Duplicating Rows!! | Excel Discussion (Misc queries) | |||
Duplicating rows and changing cells | Excel Programming |