split string separated comma

[Forestq]

Hi,

in filed I have couple of value separated by comma like below:

A1 header1	B1 header2	C1 header3
Audi	592035, 579733, 653749, 579735	20 000

<tbody>
</tbody>

If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:

A1 header1	B1 header2	C1 header3
Audi	592035	20 000
Audi	579733	20 000
Audi	653749	20 000
Audi	579735	20 000

<tbody>
</tbody>

I have:

 Set sourceWb = ActiveWorkbook
  Set ws = sourceWb.Worksheets(1)
  
  Dim LastRow As Long
  Dim MY_Split As Variant
   
  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
    For i = 2 To LastRow
        If InStr(Range("D" & i).Value, ",") Then 'find the string
            
            MY_Split = Split(Range("D" & i).Value, ",") 'split the string
            
            'MsgBox UBound(MY_Split)
            'how to add here the proper count of rows???        

                    'For countIndex = LBound(MY_Split) To UBound(MY_Split) 'here I can fill the table                                 
                    'Next
        End If
    Next i
End Sub

If you know how can I add the proper number of rows please help me.

 

[Forestq]

I added the proper number of rows but I don`t know how to put there the value

Set sourceWb = ActiveWorkbook
  Set ws = sourceWb.Worksheets(1)
  
  Dim LastRow As Long
  Dim MY_Split As Variant
   
  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
    For i = 2 To LastRow
        If InStr(Range("D" & i).Value, ",") Then 'find the string
            
            MY_Split = Split(Range("D" & i).Value, ",") 'split the string
            
                    For countIndex = LBound(MY_Split) To UBound(MY_Split)
                          ActiveCell.Offset(1).EntireRow.Insert
                    Next
        End If
    Next i