Clever Geek Handbook

Separate rows to different cells in excel using VBA

For example, I have a string variable named str. This str matters:

apple
orange
pineapple

Each word is separated with newVbLine. I want to move it to cells. A1 contains apple

, A2 contains orange

, and A3 contains pineapple

.

Thank.

+3


source to share


1 answer


The following code splits the character-delimited strings vbNewLine

in column A into column B.

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


for the image 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

enter image description here

+5


source






More articles:


All Articles