split into multiple rows (2003)

Topic

New Reply

Attached is a screen shot of what I need help with. I want to split column C so that each line is on its own row, with the data in columns A and B reapeating for each line.

Reply | Quote

Replies

Do you mean column E instead of column C? If not, I don’t understand your question.

Reply | Quote

Yes, that’s right. Typo.. and to think I even did a preview before I posted it. Thanks!!

Reply | Quote

Try this macro:

Sub SplitLines()
Dim m As Long
Dim r As Long
Dim arr As Variant
Dim i As Long
m = Range(“E” & Rows.Count).End(xlUp).Row
For r = m To 1 Step -1
arr = Split(Range(“E” & r), vbLf)
For i = UBound(arr) To 1 Step -1
Range(“A” & r).EntireRow.Copy
Range(“A” & (r + 1)).Insert
Range(“E” & (r + 1)) = arr(i)
Next i
Range(“E” & r) = arr(0)
Next r
Application.CutCopyMode = False
End Sub

Reply | Quote

It is great Hans, Melanie, I add in to my makro collection

Reply | Quote

Yes! It works spectacularly! Thank you!

Reply | Quote