Separate rows to different cells in excel using VBA
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
+5
source to share