Sub Nettoie()
Application.ScreenUpdating = False
SupprimerLignesGH0
SiGNeg
End Sub
Private Sub SupprimerLignesGH0()
With ActiveSheet.UsedRange
.Columns(2).EntireColumn.Insert 'insère une colonne auxiliaire
With .Columns(2)
.FormulaR1C1 = "=IF(AND(RC[6]=0,RC[7]=0),1,"""")"
.Value = .Value 'supprime les formules
.EntireRow.Sort .Cells, xlDescending 'tri pour regrouper et accélérer
On Error Resume Next 'si aucune SpecialCell
.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
.EntireColumn.Delete 'supprime la colonne auxiliaire
End With
End With
With ActiveSheet.UsedRange: End With 'actualise les barres de défilement
End Sub
Private Sub SiGNeg()
' Si G<0 alors H=-G et G=""
For L = 1 To Range("A65500").End(xlUp).Row
If Cells(L, "G") < 0 Then
Cells(L, "H") = -Cells(L, "G")
Cells(L, "G") = ""
End If
Next L
End Sub