Temps d'exécution de macro vba trop lente ou à améliorer

Version imprimable

Voir 40 message(s) de cette discussion en une page

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:

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

25/06/2018, 09h54
Menhir

Fais une seule boucle dans laquelle tu mets tes condition en série avec un If suivit de successions de ElseIf.
25/06/2018, 09h59
bpo2018

Citation:

Envoyé par Menhir

Fais une seule boucle dans laquelle tu mets tes condition en série avec un If suivit de successions de ElseIf.

Ouais je vois mais ça change pas le temps

Bonjour,

Pourquoi faire dix fois la même boucle ?
Plutôt que
Code:

1 2 3 4 5 6 7 8 9 10 11 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
tu peux écrire:
Code:

1 2 3 4 5 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) = "") or (Cells(i, 7) > 0 And Cells(i, 58) > 7) Then Rows(i).Delete End If Next i
A faire pour toutes tes conditions jusqu'à ce qu'il ne te reste qu'une boucle tu y gagnerais déjà du temps.
Cependant tu gardes la lenteur de la suppression ligne par ligne.
Je te conseille plutôt de passer par des filtres (regarde par exemple ici: https://www.developpez.net/forums/d1...on-lignes-vba/)

25/06/2018, 10h06
Pierre Fauconnier
Salut.

En plus de l'amélioration proposée par Menhir, je te propose d'utiliser LIKE plutôt que Left(...) pour alléger ton code (je ne dis pas que ca va plus vite, mais c'est plus court et plus lisible.

Code:

(Cells(i, 22).value like "HT*"

Et même, perso, au lieu d'une imbrication de IF, vu que tes tests portent sur la même valeur, j'utiliserais ceci à l'intérieur de la boucle, à répéter à l'intérieur de la boucle en changeant le numéro de colonne et la valeur cherchée
Code:

1 2 3 4 Select Case Left(Cells(i, 22).Value, 2) Case "H1", "H2", "H3" Rows(i).Delete End Select
25/06/2018, 11h00
Menhir

Citation:

Envoyé par bpo2018

Ouais je vois mais ça change pas le temps

Tu peux montrer le code que tu as fais ?
Ce n'est pas que je doute mais 1 boucle et 13 boucles qui s'exécutent à la même vitesse, j'ai un gros doute...

Remarque annexe

Attention que tu affectes Application.EnableEvents à iCalcul, alors que je suppose que tu voulais affecter Application.Calculation

Attention également que, sans gestion d'erreur, en cas de plantage dans la macro, les paramètres d'application ne sont pas restaurés (calculation, enableevents, ...)

Je te conseille ceci
Code:

1 2 3 4 5 6 7 8 9 10 11 12 on error goto EndHandler icalcul = application.calculation application.calculation = xlManual ... ... EndHandler: application.calculation=icalcul ... if err<>0 then ... ' gestion de l'erreur

27/06/2018, 09h15
bpo2018

J'apprécie vraiment vos idées mais malgré tt ces changements le temps attente est toujours supérieur à 50 minutes je me dis que c'est le fait d'avoir 400.000 lignes à traiter mais bon...
27/06/2018, 09h23
Pierre Fauconnier

Tu pourrais tenter de le faire en créant une colonne formulée qui reprendrait VRAI ou FAUX selon que tes conditions sont remplies ou pas.
Après cela, tri sur cette colonne et suppression en bloc des lignes souhaitées puis suppression de la colonne de tri.

Une autre façon serait d'utiliser les filtres avancés pour extraire les lignes que tu veux garder. Tu effacerais par la suite toutes les lignes du tableau de base et tu y copierais en un bloc les lignes extraites.

Ces deux options sont possibles en Excel mais peuvent être automatisées en VBA.
27/06/2018, 09h29
Menhir

Citation:

Envoyé par bpo2018

je me dis que c'est le fait d'avoir 400.000 lignes à traiter mais bon...

La taille de tes données a été multipliée par 10 en 2 jours ??? 8O
27/06/2018, 11h15
bpo2018

Citation:

Envoyé par Menhir

La taille de tes données a été multipliée par 10 en 2 jours ??? 8O

Je sais pas si t'avais bien lu le premier post si c'est pas le cas refait le

Une autre solution, encore, serait de travailler sur des tableaux VBA. Le temps de traitement devient dérisoire

Voici un code qui travaille sur 500000 lignes (une seule colonne) et qui ne garde que les valeurs < 3500 (c'est pour l'exemple). Traitement des 500000 lignes en moins d'une seconde. Il faudra évidemment adapter à ta situation (nombre de colonnes, adaptation des dimensions des tableaux, ...) mais tu pourrais gagner énormément de temps...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub Test1() Dim s, t(1 To 499999, 1 To 2) Dim i As Long, j As Long s = Range("a2:a500000") For i = 1 To 499999 If s(i, 1) <= 3500 Then j = j + 1 t(j, 1) = s(i, 1) End If Next i Rows("2:500000").Delete Range("a2:a" & j + 1).Value = t End Sub

Citation:
Envoyé par Pierre Fauconnier
Une autre solution, encore, serait de travailler sur des tableaux VBA. Le temps de traitement devient dérisoire

Voici un code qui travaille sur 500000 lignes (une seule colonne) et qui ne garde que les valeurs < 3500 (c'est pour l'exemple). Traitement des 500000 lignes en moins d'une seconde. Il faudra évidemment adapter à ta situation (nombre de colonnes, adaptation des dimensions des tableaux, ...) mais tu pourrais gagner énormément de temps...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub Test1() Dim s, t(1 To 499999, 1 To 2) Dim i As Long, j As Long s = Range("a2:a500000") For i = 1 To 499999 If s(i, 1) <= 3500 Then j = j + 1 t(j, 1) = s(i, 1) End If Next i Rows("2:500000").Delete Range("a2:a" & j + 1).Value = t End Sub
Ouais mais quand j'essaye d'elargir le truc à 100 colonnes j'ai une erreur du type mémoire insuffisante

27/06/2018, 13h43
bpo2018

Je sais pas mais je comprends pas très bien ton programme parce que la ça supprime toute les autres cases et non juste la ligne concerné et quand je mets sur plusieurs colonnes mémoire insuffisante je pense que c'est moi qui ne le traite pas bien en fait pour l'adapter à mon programme
27/06/2018, 14h13
Pierre Fauconnier

En effet, 500.000 lignes sur 100 colonnes, c'est trop pour un tableau.

Je propose alors à nouveau la solution de passer par une colonne de calcul qui reprend tes conditions et qui renvoie VRAI ou FAUX, puis un tri sur les données et une suppression en bloc des lignes VRAI ou FAUX selon ton test.

On pourrait aussi essayer un filtre avancé. Mais à mon avis pas plus rapide que la colonne formulée.
27/06/2018, 14h16
bpo2018

oui mais je sais pas comment la faire ta solution
27/06/2018, 14h26
Pierre Fauconnier
Première question: One Shot ou travail à répéter par la suite et donc intérêt d'automatiser?

Ensuite: que ne sais-tu pas réaliser dans les points suivants?
1. Ajouter une colonne à droite dans laquelle tu vas formuler tes conditions (à coup de SI, de ET, et OU, de GAUCHE) pour récupérer VRAI ou FAUX
2. Trier le tableau sr cette colonne
3. Déterminer le bloc de lignes à supprimer
4. Supprimer ces lignes
Déjà, réalise cela en Excel (sur une copie du tableau), pour voir si tu obtiens bien ce que tu désires. Si oui, on écrira ces étapes en VBA.
27/06/2018, 14h58
bpo2018

Justement je sais pas trop comment d'abord mettre plusieurs conditions si pour la meme ligne car j'ai au moins 7 conditions par ligne avec des si et des et à mettre pour que la condition soit vrai
27/06/2018, 15h01
bpo2018
Citation:
Envoyé par Pierre Fauconnier

Première question: One Shot ou travail à répéter par la suite et donc intérêt d'automatiser?

Ensuite: que ne sais-tu pas réaliser dans les points suivants?

Ajouter une colonne à droite dans laquelle tu vas formuler tes conditions (à coup de SI, de ET, et OU, de GAUCHE) pour récupérer VRAI ou FAUX
Trier le tableau sr cette colonne
Déterminer le bloc de lignes à supprimer
Supprimer ces lignes

Déjà, réalise cela en Excel (sur une copie du tableau), pour voir si tu obtiens bien ce que tu désires. Si oui, on écrira ces étapes en VBA.
oui c'est un travail à reproduire chaque matin d'où l'intéret pour moi de l'automatiser
27/06/2018, 15h05
Pierre Fauconnier

1 pièce(s) jointe(s)

Voici un exemple pour rédiger ta formule

Pièce jointe 392070

A adapter à tes cas sur les x colonnes de test ;) (Bon amusement... ;) )

Voir 40 message(s) de cette discussion en une page