Bonjour,

Cela fait plusieurs heures que je cherches à optimiser une boucle avec condition qui me permet de supprimer une ligne lorsque je rencontre une chaîne de caractères. Ces chaînes de caractères sont dans des variables de type String appelées nomX de 1 à 277 et dans un tableau (pour ma boucle de 8000 lignes).
Vous pouvez comprendre le temps que cela peut mettre pour charger.
Il faut savoir que ma recherche de caractères se fait selon 3 cellules différentes.
J'ai déjà un programme me permettant d'éviter d'afficher les actions se qui m'économise des ressources.
J'ai essayé de passé par une fonction, mais je n'ai pas trop compris le fonctionnement.
De faire une boucle avec un Dim nom (1 to 277) mais le problème c'est que ça me supprime toutes mes lignes. De passer par Select Case mais je ne gagne pas plus de temps.

Si vous avez une solution, vous êtes le bienvenue .

En attendant le 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
Sub opti()
Dim i As Integer, t As Integer, j As Integer, l As Integer, n As Integer
Dim nom1 As String, nom2  As String, nom3 As String, nom4  As String, nom5 As String, nom6 As String, nom7 As String, nom8    As String, nom9    As String, nom10   As String, nom11  As String, nom12  As String, nom13  As String, nom14  As String, nom15  As String, nom16  As String, nom17  As String, nom18   As String, nom19  As String, nom20  As String
Dim nom21   As String, nom22   As String, nom23  As String, nom24  As String, nom25  As String, nom26  As String, nom27  As String, nom28  As String, nom29  As String, nom30  As String, nom31  As String, nom32  As String, nom33  As String, nom34  As String, nom35  As String, nom36  As String, nom37  As String, nom38  As String, nom39  As String, nom40  As String
Dim nom41   As String, nom42  As String, nom43  As String, nom44  As String, nom45  As String, nom46  As String, nom47  As String, nom48  As String, nom49  As String, nom50  As String, nom51  As String, nom52  As String, nom53  As String, nom54  As String, nom55  As String, nom56  As String, nom57  As String, nom58  As String, nom59  As String, nom60  As String, nom61  As String, nom62  As String, nom63  As String, nom64  As String, nom65  As String, nom66  As String, nom67  As String, nom68  As String, nom69  As String, nom70  As String, nom71  As String, nom72  As String, nom73  As String, nom74  As String, nom75  As String, nom76  As String, nom77  As String, nom78  As String, nom79  As String, nom80  As String
Dim nom81   As String, nom82  As String, nom83  As String, nom84  As String, nom85  As String, nom86  As String, nom87  As String, nom88  As String, nom89  As String, nom90  As String, nom91  As String, nom92  As String, nom93  As String, nom94  As String, nom95  As String, nom96  As String, nom97  As String, nom98  As String, nom99  As String, nom100 As String, nom101 As String, nom102 As String, nom103 As String, nom104 As String, nom105 As String, nom106 As String, nom107 As String, nom108 As String, nom109 As String, nom110 As String, nom111 As String, nom112 As String, nom113 As String, nom114 As String, nom115 As String, nom116 As String, nom117 As String, nom118 As String, nom119 As String, nom120 As String
Dim nom121  As String, nom122 As String, nom123 As String, nom124 As String, nom125 As String, nom126 As String, nom127 As String, nom128 As String, nom129 As String, nom130 As String, nom131 As String, nom132 As String, nom133 As String, nom134 As String, nom135 As String, nom136 As String, nom137 As String, nom138 As String, nom139 As String, nom140 As String, nom141 As String, nom142 As String, nom143 As String, nom144 As String, nom145 As String, nom146 As String, nom147 As String, nom148 As String, nom149 As String, nom150 As String, nom151 As String, nom152 As String, nom153 As String, nom154 As String, nom155 As String, nom156 As String, nom157 As String, nom158 As String, nom159 As String, nom160 As String
Dim nom161  As String, nom162 As String, nom163 As String, nom164 As String, nom165 As String, nom166 As String, nom167 As String, nom168 As String, nom169 As String, nom170 As String, nom171 As String, nom172 As String, nom173 As String, nom174 As String, nom175 As String, nom176 As String, nom177 As String, nom178 As String, nom179 As String, nom180 As String, nom181 As String, nom182 As String, nom183 As String, nom184 As String, nom185 As String, nom186 As String, nom187 As String, nom188 As String, nom189 As String, nom190 As String, nom191 As String, nom192 As String, nom193 As String, nom194 As String, nom195 As String, nom196 As String, nom197 As String, nom198 As String, nom199 As String, nom200 As String
Dim nom201  As String, nom202 As String, nom203 As String, nom204 As String, nom205 As String, nom206 As String, nom207 As String, nom208 As String, nom209 As String, nom210 As String, nom211 As String, nom212 As String, nom213 As String, nom214 As String, nom215 As String, nom216 As String, nom217 As String, nom218 As String, nom219 As String, nom220 As String, nom221 As String, nom222 As String, nom223 As String, nom224 As String, nom225 As String, nom226 As String, nom227 As String, nom228 As String, nom229 As String, nom230 As String, nom231 As String, nom232 As String, nom233 As String, nom234 As String, nom235 As String, nom236 As String, nom237 As String, nom238 As String, nom239 As String, nom240 As String
Dim nom241  As String, nom242 As String, nom243 As String, nom244 As String, nom245 As String, nom246 As String, nom247 As String, nom248 As String, nom249 As String, nom250 As String, nom251 As String, nom252 As String, nom253 As String, nom254 As String, nom255 As String, nom256 As String, nom257 As String, nom258 As String, nom259 As String, nom260 As String, nom261 As String, nom262 As String, nom263 As String, nom264 As String, nom265 As String, nom266 As String, nom267 As String, nom268 As String, nom269 As String, nom270 As String, nom271 As String, nom272 As String, nom273 As String, nom274 As String, nom275 As String, nom276 As String, nom277 As String
 
Dim sh1 As Worksheet, sh2 As Worksheet
Dim BoEcran As Boolean, BoBarre As Boolean, BoEvent As Boolean, BoSaut As Boolean
Dim iCalcul As Integer
 
'**************************************
 
'optimisation (on n'affiche pas la procédure)
 
'concervation des configs existantes
BoEcran = Application.ScreenUpdating
BoBarre = Application.DisplayStatusBar
iCalcul = Application.Calculation
BoEvent = Application.EnableEvents
BoSaut = ActiveSheet.DisplayPageBreaks
 
' On force les configs
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
*************************************
nom1= "zfzvadz"
nom2="sdvsdvcsdcdc00"
.
.
.
nom3="sqcsdsc"
 
For i = 6000 To 2 Step -1
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom1 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom2 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom3 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom4 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom5 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom6 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom7 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom8 & "*") Then
    Rows(Cells(i, 3).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 3), "*" & nom9 & "*") Then 'Complement
    Rows(Cells(i, 3).Row).Delete
    End If
.
.
.
    If WorksheetFunction.CountIf(Cells(i, 2), "*" & nom275 & "*") Then 
    Rows(Cells(i, 2).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 2), "*" & nom276 & "*") Then 
    Rows(Cells(i, 2).Row).Delete
    End If
    If WorksheetFunction.CountIf(Cells(i, 2), "*" & nom277 & "*") Then 
    Rows(Cells(i, 2).Row).Delete
    End If
Next i
 
'*****************************************
 ' code défini avant d'arriver à la fin des configs restaurées
Application.ScreenUpdating = BoEcran
Application.DisplayStatusBar = BoBarre
Application.Calculation = iCalcul
Application.EnableEvents = BoEvent
ActiveSheet.DisplayPageBreaks = BoSaut
'*****************************************