Private Sub Worksheet_Activate()
Dim ncol%, tablo, i&, x$, s, j%, n&, k%
ncol = 4 'à adapter
tablo = Sheets("Feuil1").UsedRange.Resize(, ncol) 'matrice, plus rapide
ReDim resu(1 To Rows.Count, 1 To ncol)
For i = 1 To UBound(tablo)
x = tablo(i, 1)
If x <> "" Then
s = Split(tablo(i, 1))
For j = 0 To UBound(s)
n = n + 1
resu(n, 1) = s(j)
For k = 2 To ncol
resu(n, k) = tablo(i, k)
Next k, j
End If
Next i
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A1] '1ère cellule de destination, à adapter
If n Then .Resize(n, ncol) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub