Private Sub Worksheet_Activate()
Dim a As Range
Application.ScreenUpdating = False
With Feuil1.[A1].CurrentRegion 'à adapter
If .Parent.FilterMode Then .Parent.ShowAllData 'si la feuille est filtrée
.Sort .Columns(2), xlAscending, .Columns(5), , xlAscending, Header:=xlYes 'tri
.EntireColumn.Copy [A1]
End With
Application.Calculation = xlCalculationManual 'évite le recalcul des formules volatiles
With [A1].CurrentRegion
.Value = .Value 'supprime les formules
On Error Resume Next 'si aucune SpecialCell
For Each a In .Columns(9).SpecialCells(xlCellTypeBlanks).Areas
a(1, -2) = a(a.Count + 1, -2)
a(1) = a(a.Count + 1): a(a.Count + 1) = ""
Next
.Columns(9).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns(7).Resize(, 2).EntireColumn.Delete
End With
Application.Calculation = xlCalculationAutomatic
With UsedRange: End With 'actualise les barres de défilement
End Sub