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
| Option Explicit'je me suis permi
Sub Effacer()
Dim Cellule As Range, x As Long, y As Long
Dim Tb(), Tb1, Dcel As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'msgbox d'alerte
Dim msgValue
msgValue = MsgBox("Attention, vous allez effacer toutes les données saisies", vbOKCancel + vbExclamation, "ATTENTION !")
If msgValue = vbCancel Then
Exit Sub
End If
'Remise à zéro de l'onglet E3_Affectation_CI&PD
With Sheets("E3_Affectation_CI&PD")
Dcel = .Range("F" & .Rows.Count).End(xlUp).Row
Tb1 = .Range("G8:AG" & Dcel).Formula
ReDim Tb(1 To UBound(Tb1, 1), 1 To UBound(Tb1, 2))
For x = 7 To 33
For y = 8 To Dcel
Tb(y - 7, x - 6) = .Cells(y, x).Interior.ColorIndex
Next y
Next x
For x = 1 To UBound(Tb1, 2)
For y = 1 To UBound(Tb1, 1)
If Tb(y, x) = 36 Then Tb1(y, x) = ""
Next y
Next x
.Range("G8").Resize(UBound(Tb1, 1), UBound(Tb1, 2)) = Tb1
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub |
Partager