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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| Sub SupProjet()
Dim C As Range, Dico As Object, DerLig As Long
Set Dico = CreateObject("Scripting.Dictionary")
With Sheets("Feuil1")
'Recopie du projet et de la date
DerLig = .[A:I].Find("*", , , , xlByRows, xlPrevious).Row
For Each C In .Range(.[B4], .Cells(DerLig, 2))
If C.Value <> "" Or C.Offset(, 4) <> "" Then
If C.Offset(, -1) = "" Then C.Offset(, -1) = C.Offset(-1, -1)
If C.Offset(, 7) = "" Then C.Offset(, 7) = C.Offset(-1, 7)
End If
Next C
'Suppression des lignes vides
For i = DerLig To 4 Step -1
If .Cells(i, 1) = "" Then Rows(i).Delete
Next i
'On marque avec un "x" dans la date les lignes à supprimer
For Each C In .Range(.[B4], .Cells(.Rows.Count, 2).End(xlUp))
If Not Dico.Exists(C.Offset(, -1).Value) Then
Dico.Add C.Offset(, -1).Value, C.Offset(, -1).Value
.AutoFilterMode = False
.Range(.[A2], .Cells(.Rows.Count, 9).End(xlUp)).AutoFilter 1, C.Offset(, -1).Value
Set plage = .Range(.[I4], .Cells(.Rows.Count, 9).End(xlUp))
If plage.Cells.Count > 1 Then
Set plage = .Range(.[I4], .Cells(.Rows.Count, 9).End(xlUp)).SpecialCells(xlCellTypeVisible)
End If
For Each x In plage
If x.Value < Application.Subtotal(104, .Range(.[I4], .Cells(.Rows.Count, 9).End(xlUp)).SpecialCells(xlCellTypeVisible)) Then
x.Value = "x"
End If
Next x
End If
Next C
.AutoFilterMode = False
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 4 Step -1
If .Cells(i, 9) = "x" Then Rows(i).Delete
Next i
'Ajout de lignes vides
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 5 Step -1
If .Cells(i, 1) = .Cells(i - 1, 1) Then
.Cells(i, 1) = ""
.Cells(i, 9) = ""
Else
.Rows(i).Insert
End If
Next i
End With
End Sub |
Partager