Comment analyser une ligne?

Faire beaucoup d'hypothèses sur la base de ce qui semble être des informations incomplètes ici, mais selon les informations et les exemples fournis, quelque chose comme cela devrait fonctionner pour vous:

Sub tgr()

    Dim ws As Worksheet
    Dim rData As Range
    Dim aResults() As Variant
    Dim aData As Variant
    Dim vTemp As Variant
    Dim sTemp As String
    Dim ixResult As Long
    Dim i As Long, j As Long

    Set ws = ActiveWorkbook.ActiveSheet
    Set rData = ws.Range("A1").CurrentRegion
    If rData.Cells.Count = 1 Then
        ReDim aData(1 To 1, 1 To 1)
        aData(1, 1) = rData.Value
    Else
        aData = rData.Value
    End If

    ReDim aResults(1 To 65000, 1 To UBound(aData, 2))
    ixResult = 0

    For i = 1 To UBound(aData, 1)
        For Each vTemp In Split(Replace(aData(i, 1), ";#", ","), ",")
            If Len(Trim(vTemp)) > 0 Then
                ixResult = ixResult + 1
                aResults(ixResult, 1) = Trim(vTemp)
                For j = 2 To UBound(aData, 2)
                    aResults(ixResult, j) = aData(i, j)
                Next j
            End If
        Next vTemp
    Next i

    rData.Resize(ixResult).Value = aResults

End Sub