Private Sub Worksheet_Activate()
Dim ncol%, tablo, nlig&, resu(), i&, w, x, j&, h1&, h2%, y, z, n&, col%, colj%
ncol = 19 'nombre de colonnes du tableau des résultats
tablo = Sheets("STOCK").[A1].CurrentRegion.Resize(, ncol) 'matrice, plus rapide
nlig = UBound(tablo)
ReDim resu(1 To nlig, 1 To ncol)
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
Cells.Delete 'RAZ
For i = 1 To nlig
If Not IsDate(tablo(i, 1)) And InStr(tablo(i, 1), ".") = 0 Then
w = tablo(i, 1): x = tablo(i, 2)
Else
If tablo(i, 3) = "Stock début" Then
j = i
Else
For j = i + 1 To nlig
If Not IsDate(tablo(j, 1)) Or InStr(tablo(j, 1), ".") Then Exit For
Next
End If
h1 = j - i
h2 = 0
y = "": z = ""
If j <= nlig Then
If tablo(j, 3) = "Stock début" Then h2 = 5
If InStr(tablo(j, 1), ".") Then y = tablo(j, 1): z = tablo(j, 2)
End If
For j = 0 To h1 + h2
If j = h1 + h2 Then i = i + j - 1: Exit For
n = n + 1
'---mises en forme partielles---
If j = 0 And h1 Then
Cells(n, 12).Resize(h1).NumberFormat = "000"
Cells(n, 17).Resize(h1).NumberFormat = "0.000"
Cells(n, 18).Resize(h1, 2).NumberFormat = "0.000000"
ElseIf j = h1 And h2 Then
Cells(n, 7).Resize(5).NumberFormat = "0.000"
Cells(n, 8).Resize(5, 2).NumberFormat = "0.000000"
Cells(n, 10).Resize(5, 2).NumberFormat = "0.00"
Cells(n, 7).Resize(5, 5).Font.Italic = True
End If
'---------------------------------
resu(n, 1) = w
resu(n, 2) = x
resu(n, 3) = y
resu(n, 4) = z
For col = 5 To ncol
colj = IIf(j < h1, col - 4, col - 2)
resu(n, col) = tablo(i + j, colj)
Next col, j
End If
Next i
'--restitution---
With [A1] 'à adapter
If n Then
.Resize(n, ncol) = resu
.Resize(n, 2).Font.Bold = True 'police en gras
End If
End With
Columns.AutoFit 'ajustement largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub