[Solved] Excel VBA – Return separated data

by


If you want your result to be in Sheet2, then this code will do what you expect, it will check the number of Columns on Sheet1 and copy all of them into Sheet2:

Sub foo()
Dim LastRow As Long
Dim LastCol As Long
Dim ws As Worksheet: Set ws = Sheets("Sheet1") 'change this to the name of your worksheet
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'get the last row
LastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column 'get the number of columns from row 2
For i = 2 To LastRow
strTest = ws.Cells(i, 1)
Myarray = Split(strTest, Chr(10)) 'split the values on the first column into an array
    For x = LBound(Myarray) To UBound(Myarray) ' loop through array
        LastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row 'get the last row of Sheet2
        ws2.Cells(LastRow2 + 1, 1).Value = Myarray(x) 'paste the contents into Sheet2
        For y = 2 To LastCol 'loop for the number of columns on Sheet1
            ws2.Cells(LastRow2 + 1, y).Value = ws.Cells(i, y) 'paste all relevant columns into Sheet2
        Next y
    Next x
Next i
End Sub

3

solved Excel VBA – Return separated data