Excel VBA - Return separated data

Question

I am new to VBA. Can anyone help me on this? I have 'return/enter' separated data in cell A2 & A3 and corresponding data in column B, C & D. Is it possible to get desired result as in the image using Excel VBA?

Answer 1

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