Bonjour j'ai écrit une macro vba pour la suppression de lignes selon certaines conditions le problème que j'ai actuellement est au niveau du delai d'execution j'aimerais savoir si on peut le rendre plus rapide car mon fichier à environ 500000 lignes et pour traiter ça fait plus de 50 minutes trop long; sur un fichier de 45000 lignes ma macro fait 1minute et 40 seconds.
J'aimerais savoir si c'est possible de l'améliorer voici mon code:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
Sub es()
 Dim i As Long
 
' COPIE DES LIGNES DESIREES DANS LES FEUILLES DE CALCUL DEDIEES
Dim BoEcran As Boolean, BoBarre As Boolean, BoEvent As Boolean, BoSaut As Boolean
Dim iCalcul As Integer
 
 
' on conserve d'abord les configurations existantes
 
BoEcran = Application.ScreenUpdating
BoBarre = Application.DisplayStatusBar
iCalcul = Application.EnableEvents
BoSaut = ActiveSheet.DisplayPageBreaks
 
' on force les configurations
 
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
 
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
 If Left(Cells(i, 22), 2) = "HT" Or Left(Cells(i, 22), 2) = "KA" Then
Rows(i).Delete
 End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Left(Cells(i, 22), 2) = "H1" Or Left(Cells(i, 22), 2) = "H2" Or Left(Cells(i, 22), 2) = "H3" Or Left(Cells(i, 22), 2) = "H4" Or Left(Cells(i, 22), 2) = "H5" Or Left(Cells(i, 22), 2) = "H6" Or Left(Cells(i, 22), 2) = "H7" Or Left(Cells(i, 22), 2) = "H8" Or Left(Cells(i, 22), 2) = "H9" Or Left(Cells(i, 22), 2) = "H0" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 9) = "V" Or Cells(i, 9) = "T" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 45) = "7" Or Cells(i, 45) = "8" Or Cells(i, 45) = "9" Or Cells(i, 45) = " " Then
Rows(i).Delete
End If
Next i
 
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 46) = "7" Or Cells(i, 46) = "8" Or Cells(i, 46) = "9" Or Cells(i, 46) = " " Then
Rows(i).Delete
End If
Next i
 
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 47) = "7" Or Cells(i, 47) = "8" Or Cells(i, 47) = "9" Or Cells(i, 47) = " " Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 48) = "7" Or Cells(i, 48) = "8" Or Cells(i, 48) = "9" Or Cells(i, 48) = " " Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 40) = "oui" And Cells(i, 42) <> "0" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 7) <= "0" And Cells(i, 28) <> "" Then
Rows(i).Delete
End If
Next i
 
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 7) <= "0" And Cells(i, 28) = "" And Cells(i, 38) = "0" And Cells(i, 98) = "0" And Cells(i, 59) = "999" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 7) <= "0" And Cells(i, 28) = "" And Cells(i, 38) = "0" And Cells(i, 98) = "0" And Cells(i, 59) = "0" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
If Cells(i, 7) > 0 And Cells(i, 29) = 0 And Cells(i, 27) = "" Then
Rows(i).Delete
End If
Next i
 
For i = Range("i500000").End(xlUp).Row To 2 Step -1
 If Cells(i, 7) > 0 And Cells(i, 58) > 7 Then
Rows(i).Delete
 End If
Next i
 
'les configurations sont restaurées
 
Application.ScreenUpdating = BoEcran
Application.DisplayStatusBar = BoBarre
Application.Calculation = iCalcul
Application.EnableEvents = BoEvent
ActiveSheet.DisplayPageBreaks = BoSaut
 
 
End Sub