séparer une ligne en plusieurs

Private Sub Worksheet_Activate()
Dim tablo, i&, u&, h&, col%, s, j&, n&, k%
With Sheets("Base") 'nom à adapter
  tablo = .Range("A1:H" & .Cells(.Rows.Count, "G").End(xlUp).Row)
End With
If UBound(tablo) > 1 Then
  '--détermination de la hauteur---
  For i = 2 To UBound(tablo)
    u = UBound(Split(tablo(i, 7), vbLf))
    h = h + IIf(u < 0, 1, u + 1)
  Next
  '---remplissage du tableau---
  col = UBound(tablo, 2)
  ReDim R(1 To h, 1 To col)
  For i = 2 To UBound(tablo)
    s = Split(tablo(i, 7), vbLf)
    u = UBound(s)
    j = IIf(u < 0, 1, u + 1)
    For j = 1 To j
      n = n + 1
      For k = 1 To col
        R(n, k) = tablo(i, k)
      Next
      If u >= 0 Then R(n, 7) = Trim(s(j - 1))
    Next
  Next
  [A2:H2].Resize(n) = R
End If
Range("A" & n + 2 & ":H" & Rows.Count).ClearContents
End Sub

j = IIf(u < 0, 0, u)
For j = 0 To j
  n = n + 1
  For k = 1 To col
    R(n, k) = tablo(i, k)
  Next
  If u > 0 Then R(n, 7) = Trim(s(j))
Next