Macro for duplicating rows based on cell value?
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1
.Rows(i + 1).Resize(.Cells(i, "D").Value - 1).Insert
.Rows(i).Copy .Cells(i + 1, "A").Resize(.Cells(i, "D").Value -
1)
Next i
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Matt.Russett" wrote in message
...
Hello,
Below is a sample of a 200 row file I am working with.
LaneID O Zip D Zip Volume
1 44805 24210 18
2 44805 44309 12
What I need to do is duplicate the rows based on the volume for that
lane, so I can load it into an analysis tool we use.
The manual process I am currently using is to insert 17 rows after
Lane ID 1 and fill down the information so I have a total of 18 rows
for that lane. Doing that for over 200 rows is quite tedious! Does
anyone have any suggestions as to how I could set up a macro or
something so it would automatically look at the Volume column, insert
that many rows, and fill the data down?
Any suggestions are greatly appreciated!
Thanks.
|