Separate strings into different cells in excel using VBA

Question

For example, I have a string variable named str. This str has a value of:

apple
orange
pineapple

Each word is separated by a newVbLine. I want to move it on cells. A1 contains apple, A2 contains orange and A3 contains pineapple.

Thanks.

Answer 1

The code below splits strings delimited by vbNewLine in column A into column B.

Please change
ws1.[b1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)
to
ws1.[a1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)

if you want to overwrite column A

Sub Spliced()
    Dim ws1 As Worksheet
    Dim X
    Set ws1 = Sheets(1)
    X = Split(Join(Application.Transpose(ws1.Range(ws1.[a1], ws1.Cells(Rows.Count, "A").End(xlUp))), vbNewLine), vbNewLine)
    ws1.[b1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)
End Sub