XL 2021 tri par lignes

Sub Tri()
Dim Plg As Range
Dim DerCol As Long, DerLig As Long
Application.ScreenUpdating = False
DerCol = Cells(5, Columns.Count).End(xlToLeft).Column - 6
DerLig = Cells(Rows.Count, "B").End(xlUp).Row - 3
Set Plg = Range("G4").Resize(DerLig, DerCol)
Plg.Resize(1).FormulaR1C1 = "=COUNTA(R[2]C:R[" & DerLig - 1 & "]C)>0"
With ActiveWorkbook.Worksheets("aaaa").Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Plg.Resize(1), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SortFields.Add2 Key:=Plg.Offset(1).Resize(1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Plg
    .Header = xlYes
    .Orientation = xlLeftToRight
    .Apply
End With
Plg.Resize(1).ClearContents
End Sub

=SOMME(DECALER($E6;;2;):DECALER($E6;;NBVAL($G$5:$AZ$5)+1;))
au lieu de
=SOMME(G6:AZ6)

Sub TriHorizontal()
Dim dercel As Range
Set dercel = Cells.SpecialCells(xlCellTypeLastCell)
With Range([G4], dercel)
    .Rows(1) = "=SIGN(SUM(R[1]C:R" & dercel.Row & "C ))"
    .Sort .Rows(1), xlDescending, .Rows(2), , xlAscending, Header:=xlNo, Orientation:=2
    .Rows(1).ClearContents 'RAZ
End With
End Sub

Sub TriHorizontal()
Dim c As Range, P As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    If c.Interior.Color = RGB(217, 225, 242) Then Set P = Union(IIf(P Is Nothing, c, P), c)
Next
Set P = Union(P.Rows(0), P)
P.Rows(1) = "=SIGN(SUM(R[1]C:R" & P.Row + P.Rows.Count - 1 & "C ))"
P.Sort P.Rows(1), xlDescending, P.Rows(2), , xlAscending, Header:=xlNo, Orientation:=2
P.Rows(1).ClearContents 'RAZ
End Sub

Sub Trier()
Dim dercol%, P As Range, derlig&, lig&, j%, i&
Application.ScreenUpdating = False
With Sheets("aaaa")
    dercol = .Cells(5, .Columns.Count).End(xlToLeft).Column
    Set P = .Range("F4", .[F:F].SpecialCells(xlCellTypeFormulas)).Offset(, 1).Resize(, dercol - 6)
End With
derlig = P.Row + P.Rows.Count - 1
P.Rows(1) = "=SIGN(SUM(R[1]C:R" & derlig & "C ))"
P.Sort P.Rows(1), xlDescending, P.Rows(2), , xlAscending, Header:=xlNo, Orientation:=2 'tri horizontal
P.Rows(1).ClearContents 'RAZ
'---tableau de la feuille Synthèse---
With Sheets("Synthèse")
    lig = 2
    .Cells(lig, 1).Resize(.Rows.Count - lig + 1, 3).Delete xlUp 'RAZ
    For j = 1 To dercol - 6
        If Application.Count(P.Columns(j)) = 0 Then Exit For
        .Cells(lig, 1) = P(2, j)
        For i = 3 To derlig - 3
            If IsNumeric(CStr(P(i, j))) Then
                .Cells(lig, 2) = P(i, -4)
                .Cells(lig, 3) = P(i, -5)
                lig = lig + 1
            End If
    Next i, j
    .[A1].CurrentRegion.Interior.Color = RGB(226, 239, 218) 'vert
    .[A1:C1].Interior.Color = vbCyan
    .Columns("A:C").AutoFit 'ajustement largeurs
End With
End Sub