Private Sub Worksheet_Change(ByVal Target As Range)
Dim ncol%, r As Range, t, s, ub%, rest(), i&, j%, dat$, h$
ncol = 34 'colonnes C à AJ
Set r = Intersect(Target, Range("A5:A" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each r In r.Areas
t = r.Resize(, 2) 'au moins 2 éléments
ReDim rest(1 To UBound(t), 1 To ncol)
For i = 1 To UBound(t)
If t(i, 1) <> "" Then
s = Split(t(i, 1), ",")
ub = UBound(s) + 2
For j = 2 To IIf(ub < ncol - 2, ub, ncol - 2) 'colonnes D à AH
rest(i, j) = s(j - 2)
Next j
If rest(i, 9) <> "" Then rest(i, 9) = Val(rest(i, 9)) / 100 'colonne K
If rest(i, 11) <> "" Then rest(i, 11) = Val(rest(i, 11)) / 100 'colonne M
If rest(i, 26) <> "" Then rest(i, 26) = 22.5 * Val(rest(i, 26)) 'colonne AB
dat = rest(i, 5) & "/" & rest(i, 4) & "/" & rest(i, 2)
h = rest(i, 6) & ":" & rest(i, 7)
rest(i, 1) = " " 'pour ne pas laisser la cellule vide
If IsDate(dat) And IsDate(h) Then
rest(i, 1) = CDate(dat) + CDate(h)
rest(i, ncol - 1) = CDate(dat): rest(i, ncol) = CDate(h)
End If
End If
Next i
'---restitution et bordures---
r(1, 3).Resize(UBound(rest), ncol) = rest
r.Columns(3).Resize(, ncol).Borders.Weight = xlThin
If r.Row = 5 Then r(1, 3).Resize(, ncol).Borders(xlEdgeTop).Weight = xlThick
r.Columns(3).Borders(xlEdgeLeft).Weight = xlThick
r.Columns(3).Borders(xlEdgeRight).Weight = xlThick
r.Columns(ncol + 1).Borders(xlEdgeLeft).Weight = xlThick
r.Columns(ncol + 1).Borders(xlEdgeRight).Weight = xlThick
r.Columns(ncol + 2).Borders(xlEdgeRight).Weight = xlThick
Next r
'---nom défini utilisé par la fonction Col (Module1)---
Set r = Range("A" & Rows.Count).End(xlUp)
If r.Row < 5 Then Set r = [A5]
ThisWorkbook.Names.Add "DL", 0 'force le recalcul
ThisWorkbook.Names.Add "DL", r.Row
'---suppression des lignes superflues et actualisation de la barre de défilement---
Range("C" & r.Row + 1 & ":C" & Rows.Count).Resize(, ncol).Delete xlUp
With Me.UsedRange: End With
Application.Calculation = xlCalculationAutomatic
End Sub