On Oct 28, 12:33*pm, "Rick Rothstein"
wrote:
Give this macro a try (changing the Source and Destination assignments as
needed)...

Sub SplitCellLines()
* Dim Source As Range, Destination As Range, Lines As Variant

* Set Source = Worksheets("Sheet4").Range("C1")
* Set Destination = Worksheets("Sheet5").Range("C1")

* Lines = Split(Source, vbLf)
* Destination.Resize(UBound(Lines) + 1) = _
* * * * * * * * * * *WorksheetFunction.Transpose(Split(Source, vbLf))
End Sub

--
Rick (MVP - Excel)

"Mike Miller" wrote in message

...



Hi I have not done much excel programming but have a task I need some
help with. *I have a worksheet with information that I need to copy
and maniplulate onto another spreadsheet. *the data in worksheet one
looks as follows:

Row * Col1 * Col2 * *Col3
* * * * *A * * * *B * * * *1
1 * * * * * * * * * * * * * * 2
* * * * * * * * * * * * * * * 3

Basically in the source worksheet you have 3 columns and in col3 you
have some text that has new line characters. *i need to copy this row
to my new worksheet and split col3 at each new line charcter so that
it looks this in the new worksheet:

Row * Col1 * Col2 * *Col3
1 * * * *A * * * *B * * * *1
2 * * * * * * * * * * * * * * 2
3 * * * * * * * * * * * * * * 3

Any quick way to do this with VBA would be very helpful

Next question on this would be how to loop the above through a
spreadsheet with 5000 rows.

M