Sub Suppression_sous_totaux_negatifs()
Dim tablo, ub&, resu, deb&, x, i&, j&
Application.ScreenUpdating = False
With [A1].CurrentRegion
.Sort .Cells(1), xlAscending, Header:=xlYes 'tri initial
tablo = .Resize(, 2) 'matrice, plus rapide
ub = UBound(tablo)
ReDim resu(1 To ub, 1 To 1)
deb = 1: x = tablo(deb, 1)
For i = 2 To ub
If tablo(i, 1) = x Then
resu(deb, 1) = resu(deb, 1) + tablo(i, 2)
Else
resu(i, 1) = tablo(i, 2)
deb = i: x = tablo(deb, 1)
End If
Next i
For i = 2 To ub
If resu(i, 1) < 0 Then
resu(i, 1) = ""
For j = i To ub
If resu(j, 1) <> "" Then i = j - 1: Exit For
resu(j, 1) = "#N/A"
Next j
End If
Next i
.Columns(1).Insert xlToRight 'insère une colonne auxiliaire
.Columns(0) = resu 'restitution
On Error Resume Next 'si aucune SpecialCell
Union(.Columns(0), .Cells).Sort .Cells(1, 0), xlAscending, Header:=xlYes 'tri pour regrouper et accélérer
Intersect(.Columns(0).SpecialCells(xlCellTypeConstants, 16).EntireRow, .Cells).Delete xlUp 'supprime les Sous-totaux négatifs
.Columns(0).Delete xlToLeft 'supprime la colonne auxiliaire
.Sort .Cells(1), xlAscending, Header:=xlYes 'tri initial
End With
With ActiveSheet.UsedRange: End With 'actualise la barre de défilement verticale
End Sub