Private Sub Worksheet_Activate()
Dim t, i&, n&, rest(), sA, sB, sC, sE, sF, sH, sI, sJ, sK, j
Application.ScreenUpdating = False
On Error Resume Next
'---préparation---
[A:K].ClearContents 'RAZ
With Feuil1.UsedRange.Resize(, 11) 'tableau de la feuille Original
[A1].Resize(.Rows.Count, 11) = .Value 'copie les valeurs
End With
[E:E].Replace "€", "€" & vbLf
[E:E].Replace String(3, vbLf), String(2, vbLf)
t = Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row) 'matrice plus rapide, 1 colonne en plus
'---détermination du nombre de lignes du tableau rest---
For i = 1 To UBound(t)
t(i, 12) = Len(t(i, 5)) - Len(Replace(t(i, 5), vbLf, ""))
n = n + t(i, 12) + 1
Next
ReDim rest(1 To n, 1 To 11)
n = 1
'---remplissage du tableau rest---
For i = 1 To UBound(t)
sA = Split(t(i, 1), vbLf): sB = Split(t(i, 2), " E"): sC = Split(t(i, 3), vbLf)
sE = Split(t(i, 5), vbLf): sF = Split(t(i, 6), vbLf): sH = Split(t(i, 8), vbLf)
sI = Split(t(i, 9), vbLf): sJ = Split(t(i, 10), vbLf): sK = Split(t(i, 11), vbLf)
For j = 0 To t(i, 12)
If IsDate(sA(j)) Then rest(n + j, 1) = CDate(sA(j)) Else rest(n + j, 1) = sA(j)
rest(n + j, 2) = LTrim(sB(j)): rest(n + j, 3) = sC(j)
rest(n + j, 5) = sE(j): rest(n + j, 9) = sI(j)
If IsNumeric(sF(j)) Then rest(n + j, 6) = CDbl(sF(j)) Else rest(n + j, 6) = sF(j)
If IsNumeric(sH(j)) Then rest(n + j, 8) = CDbl(sH(j)) Else rest(n + j, 8) = sH(j)
If IsNumeric(sJ(j)) Then rest(n + j, 10) = CDbl(sJ(j)) Else rest(n + j, 10) = sJ(j)
If IsNumeric(sK(j)) Then rest(n + j, 11) = CDbl(sK(j)) Else rest(n + j, 11) = sK(j)
Next j
n = n + j
For j = 1 To 11
If rest(n - 1, j) <> "" Then Exit For
Next j
If j = 12 Then n = n - 1 'suppression si la dernière ligne est vide
Next i
'---restitution---
[A1].Resize(UBound(rest), 11) = rest
Columns("A:K").AutoFit 'ajustement largeur
With Me.UsedRange: End With 'actualise la barre de défilement verticale
End Sub