Sub Moyenne()
Dim I As Long, [COLOR="Red"]dercol As Byte,[/COLOR] plage As Range
Application.ScreenUpdating = False
With Sheets("total")
For I = .Range("E65536").End(xlUp).Row To [COLOR="Red"]4[/COLOR] Step -1
If .Cells(I, 5) Like "Moyennes *" Then .Cells(I, 5).EntireRow.Delete 'suppression des lignes de moyennes
Next
[COLOR="Red"] .Rows("4:65536").Sort Key1:=.Range("E4"), Order1:=xlAscending, _
Key2:=.Range("A4"), Order2:=xlAscending, Header:=xlNo 'tri du tableau[/COLOR]
[COLOR="Red"]dercol = .Range("IV3").End(xlToLeft).Column 'dernière colonne à remplir[/COLOR]
For I = .Range("E65536").End(xlUp).Row + 1 To [COLOR="Red"]5[/COLOR] Step -1
If .Cells(I - 1, 5) <> .Cells(I, 5) Then
.Rows(I).Insert
.Rows(I).Borders(xlInsideVertical).LineStyle = xlNone 'supprime les bordures
.Cells(I, 1).Resize(, dercol).Interior.ColorIndex = 36 'colore la ligne en jaune
.Cells(I, 5) = "Moyennes " & .Cells(I - 1, 5)
If Not plage Is Nothing Then _
plage.FormulaR1C1 = "=AVERAGE(R[" & I - plage.Row + 1 & "]C:R[-1]C)"
Set plage = .Range(.Cells(I, 7), .Cells(I, [COLOR="Red"]dercol[/COLOR]))
End If
Next
plage.FormulaR1C1 = "=AVERAGE(R[" &[COLOR="Red"] 4 [/COLOR]- plage.Row & "]C:R[-1]C)" 'il y a encore une ligne de moyennes à remplir...
End With
End Sub