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

**bpo2018** · 25/06/2018, 09h27

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

**Menhir** · 25/06/2018, 09h54

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

**bpo2018** · 25/06/2018, 09h59

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

**Menhir** · 25/06/2018, 11h00

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...

**halaster08** · 25/06/2018, 10h04

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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/)

**Pierre Fauconnier** · 25/06/2018, 10h06

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

(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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
  Select Case Left(Cells(i, 22).Value, 2)
    Case "H1", "H2", "H3"
      Rows(i).Delete
  End Select

Invité · 28/06/2018, 17h46

Bonsoir Pierre,
En fait j'ai voulu lui montrer l'inutilité de multiple boucle en reprenant son code dans une seule boucle.

Ici nous avons dune base de données avec champs calculé et un volume de données presque aberrant pour un fichier Excel.

Vue le volume,bien Access me paresse plus confirme pour ce genre de traitement, j'aurais fais une requête sql update(ColonneA='Kill") avec Thisworbook.fullname en connexion

Ensuite for i step -1 pour supprimer les lignes.

Mais ajouter une colonne calculer me parait bien également.

Si les données proviennent effectivement d'un ERP, oui revoir la méthode d'import.

**Pierre Fauconnier** · 25/06/2018, 11h05

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**bpo2018** · 27/06/2018, 09h15

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...

**Menhir** · 27/06/2018, 09h29

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 ???

**bpo2018** · 27/06/2018, 11h15

Envoyé par Menhir

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

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

Invité · 28/06/2018, 18h25

J'ai proposé une solution similaire à un quidams se ce forum, pour cette fois un export vers Access.

Maintenant si c'est un fichier texte requêté dessus n'est pas un problème pour l'import dans Excel.

**Pierre Fauconnier** · 27/06/2018, 09h23

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.

**Pierre Fauconnier** · 27/06/2018, 11h50

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**bpo2018** · 27/06/2018, 13h31

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**bpo2018** · 27/06/2018, 13h43

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

**Pierre Fauconnier** · 27/06/2018, 14h13

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.

**bpo2018** · 27/06/2018, 14h16

oui mais je sais pas comment la faire ta solution

**Pierre Fauconnier** · 27/06/2018, 14h26

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.

**bpo2018** · 27/06/2018, 14h58

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

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

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager