Splitting values within Cell in excel into rows whilst keeping other column data

Question

I want to do the following in a non-manual way:

I have a cell with multiple values separated by line breaks which I want to split up BUT keep the values in the other columns the same.

Thank you

Answer 1

As SJR pointed out, the following should work:

Sub Foo()
    'working for active sheet
    'copy to the end of sheets collection
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    Dim tmpArr As Variant
    Dim Cell As Range
    For Each Cell In Range("B1", Range("B2").End(xlDown))
        If InStr(1, Cell, Chr(10)) <> 0 Then
            tmpArr = Split(Cell, Chr(10))

            Cell.EntireRow.Copy
            Cell.Offset(1, 0).Resize(UBound(tmpArr), 1). _
                EntireRow.Insert xlShiftDown

            Cell.Resize(UBound(tmpArr) + 1, 1) = Application.Transpose(tmpArr)
        End If
    Next
    Application.CutCopyMode = False
End Sub