Splitting a single rows data into multiple rows

[santanuKD]

Hi -

Need is to split the data in Cell "J" to multiple rows along with other column Data.
Cell Data should be moved to other Rows when the Line starts with Key Word "Step".

Please suggest. Attaching the file for reference.

on a new sheet:

Sub SplitData()
    Dim arrColC As Variant
    Dim shDATA As Worksheet
    Dim r As Long, c As Long, i As Long
    
    Set shDATA = Sheets("Data Sheet") 'change as needed
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("SPLIT SHEET").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=shDATA
    ActiveSheet.Name = "SPLIT SHEET"
    
    i = 1
    For r = 1 To shDATA.Cells(Rows.Count, "A").End(xlUp).Row
        arrColC = Split(shDATA.Cells(r, 3), ",")
        For c = 0 To UBound(arrColC)
            Cells(i, 1) = shDATA.Cells(r, 1)
            Cells(i, 2) = Format(shDATA.Cells(r, 2), "d-mmm-yy")
            Cells(i, 3) = arrColC(c)
            i = i + 1
        Next c
    Next r
End Sub

Split from https://www.mrexcel.com/forum/excel...to-multiple-rows-post3177907.html#post3177907