1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| Option Explicit
Dim x As Integer, cel As Range, tb()
Sub Maj()
With Sheets("Feuil1")
x = .Range("G" & .Rows.Count).End(xlUp).Row
Sup_doublons .Range("G2:G" & x)
End With
Application.Calculation = xlCalculationManual
With Sheets("synthèse")'Attention correction du mot synthèse, snon remplacer par ton nom de feuille
x = .Range("A" & .Rows.Count).End(xlUp).Row
If x > 1 Then
.Range("A2:B" & x).ClearContents
End If
.Range("A2").Resize(UBound(tb), 1) = Application.Transpose(tb) '
x = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("B2:B" & x).FormulaLocal = "=SOMME.SI(Feuil1!$G$2:$G$646;A2;Feuil1!$H$2:$H$646)" 'totoal en col B
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Sup_doublons(Plg As Range)
Dim Un As Collection
x = 0
Set Un = New Collection
On Error Resume Next
With Sheets("Feuil1")
For Each cel In Plg
Un.Add cel, CStr(cel)
If Err = 0 Then
x = x + 1
ReDim Preserve tb(1 To x)
tb(x) = cel
End If
Err.Clear
Next cel
End With
Set Un = Nothing
End Sub |
Partager