[ask]split text with comma to new rows

[qiyusi]

Hello Again,

I have a macro which work to split text with comma to new row, but not work properly. please review the code and screenshot below.
Here is the original data
rawdata.PNG

Here is the result after macro executed
macroprocess.PNG
Here is the format i want
formatneed.PNG

macro code:

Sub split_rows()
Dim SearchRange As Range, CCell As Range, x As Long, y As Long, MyArr As Variant
Set SearchRange = Application.InputBox("Click in the column to split by", Type:=8)
For Each CCell In SearchRange.Cells
    MyArr = Split(CCell, ",")
    If UBound(MyArr) > 0 Then
        CCell = MyArr(LBound(MyArr))
        For x = LBound(MyArr) + 1 To UBound(MyArr)
            CCell.Offset(x, 0).EntireRow.Insert
            CCell.Offset(x, 0) = MyArr(x)
           
        Next x
    End If
Next CCell
End Sub

I am thinking how to change Entirerow.insert as this is causing problem when work to multiple cell with coma.

Appreciate for any respond.
Thanks

[jolivanes]

Maybe

Sub vbaexpress_52132()
Dim j As Long, jve
    For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        jve = Split(Cells(2, j), ",")
    Cells(2, j).Resize(UBound(jve) + 1) = Application.Transpose(jve)
Next j
End Sub

[qiyusi]

Hello, sorry for late reply.

The code works! thanks !