Trucs pour boucle for n=1 to 55000 (?)

Kargos · 13 Juillet 2010

Bonjour à tous et merci de prendre quelques instants pour lire ces lignes,

J'ai dernièrement découvert les vertus de l'utilisation de Macro en Excel et depuis, j'en abuse (!)

Toutefois, je suis présentement confronté à un problème de durée d'exécution dans une boucle "For ... Next". Et je me demande si certains d'entre vous n'auraient pas des trucs pour faire sauver du temps.

Je vous explique mon cas:
Sous Excel 2003, je crée une feuille excel en prenant des informations dans différents rapports provenant de bases de données diverses et donc, avec des formats différents.
Pour pouvoir rendre mon fichier excel fonctionnel, j'essaie de lui donner une allure plus "belle".
Par contre, à un certain point dans mon exécution de code, je me retrouve avec environ 55 000 lignes de données dont environ 25 000 sont vides.
Je souhaite donc supprimer toutes les lignes vides.

Petit hic, dans mes lignes non-vides, les cellules ne sont pas toutes remplies, et donc je ne peux pas utiliser la fonction IsEmpty() sur une seule cellule de la ligne. J'ai aussi essayé d'utiliser IsEmpty() sur toutes les cellules de la ligne (12) avec un code du genre:

For n = 55 000 To 1 Step -1
If IsEmpty(Cells(n,1)) and IsEmpty(Cells(n,2)) and ... and IsEmpty(Cells(n,12) Then
Rows(n).Delete
End If
Next

Présentement, mon code ressemble à ceci:

For n = 55 000 To 1 Step -1
Set verif = Range(Cells(n, 1), Cells(n, 15))
If WorksheetFunction.CountBlank(verif) = verif.Count Then
Rows(n).Delete
End If
Next

J'ai aussi essayé avec

For n = 55 000 To 1 Step -1
Set verif = Range(Cells(n, 1), Cells(n, 15))
If Application.Counta(verif) = 0 Then
Rows(n).Delete
End If
Next

Si vous connaissez des trucs pour faire exécuter ce genre de code plus rapidement, je vous prie de bien vouloir me les partager. Je vous en serais extremement reconnaissant.

Merci Beaucoup

smotty · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Bonjour le forum, Roger2327,

En fait laisse tomber la fersion avec filtre.

Elle fonctionne mais la suppression bloque s'il y a trop de ligne à supprimer. Excel trouve que c'est trop complexe à gérer étant donné que ces lignes sont discontinues, dommage car c'était instantanné comme résultat. A moins que quelqu'un trouve une solution pour pallier à ce souci je serai très intéressé de la connaître.

Bref c'est une super méthode mais pas pour supprimer.

Donc soit le trie convient soit on utilise une autre méthode plus "classique".

Par contre, la première version ne passe vraiment pas. Est-elle faite pour Excel2003 ou pour une autre version ?

Elle a été faite sur 2007 mais je ne vois pas de raisons que ça ne fonctionne pas sur 2003. Peut-être au niveau du tri qui peut être légèrement différent

J'ai repris le tri d'un enregistrement de macro. Donc essaye de faire un enregistrement de macro sur 2003 pour voir la différence.
A priori ça a fonctionné pour Kargos.

A+

smotty

ROGER2327 · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re...

Merci pour l'explication.
Le problème vient effectivement du tri sous Excel2007 qui fonctionne différemment de celui d'Excel2003.
Dommage que le fait que le code concerne Excel2007 n'ait pas été mentionné plus tôt ! On n'est pas ici sur le forum Spécial 2007...

ROGER2327
#3906

Lundi 2 Tatane 137 (Commémoration du Père Ebé, SQ)
27 Messidor An CCXVIII
2010-W28-4T07:51:40Z

smotty · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Dommage que le fait que le code concerne Excel2007 n'ait pas été mentionné plus tôt

J'étais loin de penser que le tri se faisait différemment entre les 2 versions.

Chaque fois que je l'utilise en vba, je me contente d'utiliser l'enregistreur de macro et de l'adapter. ça ne m'a pas sauté aux yeux...

on en apprend tous les jours

ROGER2327 · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re...

Ce n'est pas grave, mais préciser sur quelle version les solutions sont testées évite les malentendus.

Cordialement,
ROGER2327
#3907

Lundi 2 Tatane 137 (Commémoration du Père Ebé, SQ)
27 Messidor An CCXVIII
2010-W28-4T08:16:17Z

JNP · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re

,
@ Smotty : Pour info, 2007 (comme 2010) n'est pas limité à 3 critères de tri, c'est pour cela que le code change litéralement d'un tri où les 3 critères sont énoncés à une addition de critères dans la propriété SortFields

Code:

.Sort.SortFields.Add

@ Kargos : Je m'étonne de ne pas avoir eu de retour sur ma proposition en #3. Sur 55000 lignes, elle s'éxécute en 2, 3 secondes. Y-aurait-il une plus grande différence sur ton PC ?
Bonne journée

smotty · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Bonjour JNP,

Merci pour l'info...

Dépasser ses certitudes et s'ouvrir à l'improbable.

c'est ainsi que je résumerai cette leçon...

smotty · 15 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re

Juste pour le fun, j'ajoute la solution proposée avec le tri et conserve des données dans l'ordre. (Fait sur 2007, à adapter concernant le tri)

Code:

Sub SupprimeVides()
    Dim C As Range
    Dim l As Long
    
    Application.ScreenUpdating = False
    l = [A65535].End(xlUp).Row
    
'colonne iniquant le nombre de cellules non vides
    Range("O1:O" & l).FormulaR1C1 = "=COUNTA(RC[-14]:RC[-1])"
'colonne indiquant l'ordre à conserver des données
    Range("P1").Value = 1
    Range("P2:P" & l).FormulaR1C1 = "=R[-1]C+1"

'conserve uniquement les valeurs
    Range("O1:P" & l).Copy
    Range("Q1").PasteSpecial Paste:=xlPasteValues
    Range("Q1:R" & l).Copy Range("O1")
    Range("Q1:R" & l).Clear
    Application.CutCopyMode = False
    
'trie selon la colonne des nombre de cellules non vides
    ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=Range("O1:O" & l) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Feuil1").Sort
        .SetRange Range("A1:P" & l)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    'cherche le premier zero et delete toutes les lignes suivantes
    Set C = Range("O:O").Find(0)
    
    If Not C Is Nothing Then
        Rows(C.Row & ":" & l).Delete
        
        l = [O65535].End(xlUp).Row
        
'trie selon l'ordre d'origine
        ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add Key:=Range("P1:P" & l), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Feuil1").Sort
            .SetRange Range("A1:P" & l)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    
    Range("O1:P" & l).Clear
    Application.ScreenUpdating = True

End Sub

Tout ça en un temps record

A+

smotty

Kargos · 16 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

JNP à dit:
Re ,

@ Kargos : Je m'étonne de ne pas avoir eu de retour sur ma proposition en #3. Sur 55000 lignes, elle s'éxécute en 2, 3 secondes. Y-aurait-il une plus grande différence sur ton PC ?
Bonne journée

En fait, peut-etre effectivement que la différence entre nos PC est énorme, mais j'ai du interrompre ton code apres 6 minutes d'exécution, alors qu'il avait parcouru la moitié des lignes seulement.

Je ne saurais expliquer pourquoi...

JNP · 16 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re

,

Kargos à dit:
En fait, peut-etre effectivement que la différence entre nos PC est énorme, mais j'ai du interrompre ton code apres 6 minutes d'exécution, alors qu'il avait parcouru la moitié des lignes seulement.

Je ne saurais expliquer pourquoi...

Merci de ton retour. Moi non plus, ne ne vois pas le pourquoi

...
De 2, 3 secondes à 6 minutes...
Si par hasard, d'autres peuvent tester

Bonne soirée

smotty · 16 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Bonsoir JNP, le forum,

J'ai testé également sur 55000 lignes et effectivement j'ai dû stopper le prgramme.

Le nombre de lignes vides est de l'ordre de 20000 à 250000. As-tu essayé avec autant de lignes vides?

Perso, ta méthode m'a intéressé car je n'ai jamais utilisé "Union" donc je garde ça au chaud

A+

smotty

laetitia90 · 17 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

bonjour tous ,
JNP j'ai teste avec mon tableau 65000 lignes pour faire des essais .pour faire le plus difficile possible une ligne vide sur 2
sur 1000 lignes 5 secondes
sur 2000 lignes 10 secondes
jusqu'a la assez lineaire!!!
sur 4000 lignes 35 secondes
sur 10000 lignes 245 secondes
j'ai arrete la... si je fais une corrélation avec mon pc & si tu obtenais 2 secondes
tu as du tester sur environ 700 lignes estimation personnel

a+ leti

JNP · 17 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re

laetitia90 à dit:
JNP j'ai teste avec mon tableau 65000 lignes pour faire des essais .pour faire le plus difficile possible une ligne vide sur 2
tu as du tester sur environ 700 lignes estimation personnel

D'abord, merci de ton test.
Non, c'est bien sur 55000 lignes, mais...
Je viens de refaire des essais, en réalité, tout dépends des conditions du test

... Comme tu dis avec 1 ligne sur 2.
Les premières fois, j'avais rempli 55000 lignes et vidé une vingtaine de ligne au hasard, résultat 2 secondes.
Cette fois-ci, j'ai vidé par macro 1 ligne sur 10, et là, ça change tout...
Donc la lenteur est proportionnelle au nombre de lignes vides rencontrés.
Donc, je peux mettre mon code à la poubelle, tout au moins sur des fichiers de cette taille

...
Bon WE (orage et pluie ce matin...)

laetitia90 · 17 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

bonjour tous,
pour JNP par curiosité j'ai refais des tests sur 15 premiéres colonnes en utilisant seulement un "tablo" sans formules si tu pouvais tester

Code:

Sub essai()
  Dim t As Variant, t2() As String, x As Long, i As Long, k As Long
  Application.ScreenUpdating = False
  On Error Resume Next
  t = Range("a1:p" & Cells.Find("*", , , , , xlPrevious).Row)
  x = 1
  For i = 1 To UBound(t)
  If t(i, 1) <> "" Or t(i, 2) <> "" Or t(i, 3) <> "" Or t(i, 4) <> "" Or t(i, 5) <> "" _
  Or t(i, 6) <> "" Or t(i, 7) <> "" Or t(i, 8) <> "" Or t(i, 9) <> "" Or t(i, 10) <> "" _
  Or t(i, 11) <> "" Or t(i, 12) <> "" Or t(i, 13) <> "" Or t(i, 14) <> "" Or t(i, 15) <> "" Then
  ReDim Preserve t2(1 To 15, 1 To x)
  For k = 1 To 15
  t2(k, x) = t(i, k): Next k: x = x + 1: End If: Next i
  Columns("A:P").ClearContents
 Range("a1").Resize(UBound(t2, 2), UBound(t2, 1)) = Application.Transpose(t2)
 Erase t, t2
End Sub

JNP · 17 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Re

,

laetitia90 à dit:
pour JNP par curiosité j'ai refais des tests sur 15 premiéres colonnes en utilisant seulement un "tablo" sans formules si tu pouvais tester

Environ 6 secondes sur 55000 lignes.
Bon WE

Fo_rum · 17 Juillet 2010

Re : Trucs pour boucle for n=1 to 55000 (?)

Salut

autre exemple (les formats ne sont pas conservés).

Effectuez une recherche sur Excel Downloads...

Trucs pour boucle for n=1 to 55000 (?)

Kargos

XLDnaute Nouveau

smotty

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

smotty

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

JNP

XLDnaute Barbatruc

smotty

XLDnaute Occasionnel

smotty

XLDnaute Occasionnel

Kargos

XLDnaute Nouveau

JNP

XLDnaute Barbatruc

smotty

XLDnaute Occasionnel

laetitia90

XLDnaute Barbatruc

JNP

XLDnaute Barbatruc

laetitia90

XLDnaute Barbatruc

JNP

XLDnaute Barbatruc

Fo_rum

XLDnaute Accro

Pièces jointes

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums