Split Multiline Cell text Into Multiple Rows

[john3j]

Cell A5 contains the following multiline text:

This
Is
A
Test

I am trying to figure out how to get the following:

Range("A5").Value = "This"
Range("A6").Value = "Is"
Range("A7").Value = "A"
Range("A8").Value = "Test"

Is there a way to select the contents of a multi line cell and paste it so that each line is in a new row?

[mancubus]

Sub vbax_52645_Split_Write()

Dim SplitText

SplitText = Split(Range("A5").Value, vbLf)
Range("A5").Resize(UBound(SplitText) + 1).Value = Application.Transpose(SplitText)

End Sub

[john3j]

That is EXACTLY what I was looking for! Thank you so much!

[mancubus]

you are welcome.

please mark the thread as solved from Thread Tools for future references.