Separate comma-delimited values to rows

Hey Sporto
The procedures are built on your sample file. Column E was empty in your sample so i used it for other "stuff" and then I cleared my other "stuff" here

Cells(Z, 5) = daAnsw

and here

Columns(5).ClearContents

So, to make the procedure work, I'll probably need a version of your "real" data (proprietary info removed) so I can accommodate the procedure to your "real" data. Can you provide this?
John

Hi Sean
Take a look at this and see if it works. As always, let me know of issues.
John

This routine worked for me.

Sub CommaDelimitedToRows()
    Const Delimiter As String = ","
    Dim dataRange As Range
    Dim numberArray As Variant, numberRange As Range
    Dim oneCell As Range
    Dim rowNum As Long
    
    With ThisWorkbook.Sheets("DATA").Range("A:C")
        Set dataRange = Range(.Cells(2, .Columns.Count), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With dataRange
        For rowNum = .Rows.Count To 1 Step -1
            With .Cells(rowNum, 1)
                numberArray = Split(CStr(.Value), ",")
                Set numberRange = Range(.Cells, .Offset(UBound(numberArray), 0))
                
                If 0 < UBound(numberArray) Then
                    With numberRange
                        .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.Insert shift:=xlDown
                    End With
                    numberRange.Cells(1, 1).Resize(UBound(numberArray) + 1, 1).Value = Application.Transpose(numberArray)
                End If
            End With
        Next rowNum
    End With
    
    With dataRange.EntireColumn.Columns(1)
        Set dataRange = Range(dataRange, .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With dataRange
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        On Error GoTo 0
        .Value = .Value
    End With
End Sub

To handle a varying number of columns (if the max number of columns is in the top row) change this first section

With ThisWorkbook.Sheets("DATA").Range("A:C")
        Set dataRange = Range(.Cells(2, Columns.Count).End(xlToLeft), .Cells(.Rows.Count, 1).End(xlUp))
    End With