Break one line item into many...
 

Is there a way or a script I can run to break out one line item into many?
For example

A B (columns)
2 A5285
3 A2358

I would like Excel to recognize that I want 2 lines created from colunm A
and fill A5285 into column B next to it. And subsequently I would like it to
recognize 3 and create three lines from it etc..etc.. So it would look as
below

A B
2 A5285
2 A5285
3 A2358
3 A2358
3 A2358

Thanks in advance!!

Tom

Break one line item into many...
 

I'm not the best VBA programmer but this seems to work ok on my end.

Sub buildlist()
Dim rng As Range
Dim cell As Range, rw As Long
Dim sh As Worksheet, i As Long
Set sh = Worksheets("Sheet2")
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
rw = 2
sh.Range("A2:B65536").ClearContents
For Each cell In rng
For i = 1 To cell.Offset(0, 0).Value
sh.Cells(rw, 1).Value = cell.Value
sh.Cells(rw, 2).Value = cell.Offset(0, 1).Value
rw = rw + 1
Next i
Next cell
End Sub

Assumptions:

Data is on Sheet1 in the range A2:Bn and is a contiguous block (the macro
stops "looking" for data when it encounters an empty cell in column A)
The list will be output to Sheet2 starting in cell A2. So, you need to make
sure Sheet2 exists!
Each time the macro is run it will delete the previous list.

Try this on a COPY of your file first!

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Is there a way or a script I can run to break out one line item into many?
For example

A B (columns)
2 A5285
3 A2358

I would like Excel to recognize that I want 2 lines created from colunm A
and fill A5285 into column B next to it. And subsequently I would like it
to
recognize 3 and create three lines from it etc..etc.. So it would look as
below

A B
2 A5285
2 A5285
3 A2358
3 A2358
3 A2358

Thanks in advance!!

Tom

Break one line item into many...
 

Someone at this DG gave me this macro back in January (or somewhere around
that time). Hope it works for you...


Sub InsertAnyRows()

Dim insertNumber As Range
Dim insertStart As Range
Dim redRng As Range
Dim i As Integer

Set insertNumber = Application.InputBox _
(Prompt:="Select a point to begin inserting rows. For instance, choose first
non blank cell in Column A", Title:="Add a row", Type:=8)
insertNumber.Select
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Dim MyRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
MyRow = 1
Do Until MyRow = lastcell
For i = 1 To Cells(MyRow, 1)

If Cells(MyRow, 1) < "" Then
Cells(MyRow + 1, 1).Select
Selection.EntireRow.Insert shift:=xlDown
End If

Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
MyRow = MyRow + 1
Loop

End Sub


All you have to do is split the data so that the number shows in Column A
and you can do this by the following method: Data Text to Columns.

Hope it works for you.

Ryan--




"Tom" wrote:

Is there a way or a script I can run to break out one line item into many?
For example

A B (columns)
2 A5285
3 A2358

I would like Excel to recognize that I want 2 lines created from colunm A
and fill A5285 into column B next to it. And subsequently I would like it to
recognize 3 and create three lines from it etc..etc.. So it would look as
below

A B
2 A5285
2 A5285
3 A2358
3 A2358
3 A2358

Thanks in advance!!

Tom