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.
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
