Conversion des données séparées par ; en ligne

Sub test()
Dim i As Integer, j As Integer, strMem As String, lineAdded As Boolean, tabStr() As String
With ThisWorkbook.Sheets("test")
    For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
        lineAdded = False
        For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
            strMem = .Cells(i, j).Text
            If InStr(strMem, ";") > 0 Then
                If Not lineAdded Then .Rows(i + 1).Insert: lineAdded = True
                tabStr = Split(strMem, ";")
                .Cells(i, j) = tabStr(LBound(tabStr))
                If lineAdded Then .Cells(i + 1, j) = tabStr(UBound(tabStr))
            Else
                If lineAdded Then .Cells(i + 1, j) = strMem
            End If
        Next j
    Next i
End With
End Sub

Sub convertir()
Dim i As Integer, j As Integer, k As Integer, decalage As Integer, strMem As String, lineAdded As Boolean, tabColA() As String, tabColB() As String
With ThisWorkbook.Sheets("Feuil1")
    For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
        tabColA = Split(.Range("A" & i).Text, ";")
        tabColB = Split(.Range("B" & i).Text, ";")
        For j = 1 To (UBound(tabColA) - LBound(tabColA) + 1) * (UBound(tabColB) - LBound(tabColB) + 1) - 1
            .Rows(i + 1).Insert
            .Rows(i).Copy .Rows(i + 1)
        Next j
        decalage = 0
        For j = LBound(tabColA) To UBound(tabColA)
            For k = LBound(tabColB) To UBound(tabColB)
                .Range("A" & i).Offset(decalage, 0).Value = tabColA(j)
                .Range("B" & i).Offset(decalage, 0).Value = tabColB(k)
                decalage = decalage + 1
            Next k
        Next j
    Next i
End With
End Sub

Application.ScreenUpdating = False

Application.ScreenUpdating = True

Sub convertir()
Dim i As Integer, j As Integer, decalage As Integer, strMem As String, lineAdded As Boolean

'déclarer autant de tableau que de colonne contenant des valeurs concaténées
Dim tabColA() As String, tabColB() As String, tabColAL() As String, tabColAM() As String
'déclarer autant de variable que de colonne contenant des valeurs concaténées
Dim iA As Integer, iB As Integer, iAL As Integer, iAM As Integer


'masquer l'affichage
Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Feuil1")

    'ce "For" permet de boucler sur ligne de la feuille "Feuil1" (de 13 à 2 dans cet exemple)
    For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
    
        'récupérer dans les tableaux "tabColX" les items de la colonne "X" de la ligne i
        '(c'est ici qu'on sépare les items séparés par un ";")
        tabColA = Split(.Range("A" & i).Text, ";")
        tabColB = Split(.Range("B" & i).Text, ";")
        tabColAL = Split(.Range("A" & i).Text, ";")
        tabColAM = Split(.Range("A" & i).Text, ";")
        
        'INSERER LES NOUVELLES LIGNES
        'cette bouclepermet de calculer le nombre de ligne à insérer en fonction des différentes combinaisons
        For j = 1 To _
            (UBound(tabColA) - LBound(tabColA) + 1) * _
            (UBound(tabColB) - LBound(tabColB) + 1) * _
            (UBound(tabColAL) - LBound(tabColAL) + 1) * _
            (UBound(tabColAM) - LBound(tabColAM) + 1) - 1
            'insérer une ligne après la ligne i
            .Rows(i + 1).Insert
            'copier les valeur de la ligne i (dans la ligne insérée)
            .Rows(i).Copy .Rows(i + 1)
        Next j
        
        'MODIFIER LES COLONNES "A COMBINAISON"
        'initialiser la variable "decalage"
        decalage = 0
        'boucler sur chaque combinaison posible
        For iA = LBound(tabColA) To UBound(tabColA)
            For iB = LBound(tabColB) To UBound(tabColB)
                For iAL = LBound(tabColA) To UBound(tabColA)
                    For iAM = LBound(tabColB) To UBound(tabColB)
                        'inscrire les différentes combinaisons sur la ligne    "i" + "decalage"
                        .Range("A" & i).Offset(decalage, 0).Value = tabColA(iA)
                        .Range("B" & i).Offset(decalage, 0).Value = tabColB(iB)
                        .Range("AL" & i).Offset(decalage, 0).Value = tabColAL(iB)
                        .Range("AM" & i).Offset(decalage, 0).Value = tabColAM(iB)
                        'incrémenter la variable "decalage"
                        decalage = decalage + 1
                    Next iAM
                Next iAL
            Next iB
        Next iA
        
    'passer à l'autre ligne (de la feuille ("Feuil1")
    Next i
End With

'raffraichir l'affichage
Application.ScreenUpdating = True

End Sub