Accelérer l'éxecution d'une macro

Bonjour,

Le principe est assez simple,

j'ai une macro "recap" qui liste les références identique, le souci c'est que dans ma base de donnée j'ai plus de 100000 lignes et que ça bug au dessus de 5000 lignes donc je souhaiterais savoir si il y a des astuces pour faire un code plus léger.

Merci d'avance pour votre aide

vbSub recap()
Application.ScreenUpdating = False
Dim decalage As Integer
Dim ligne As Integer
Dim ligne_test As Integer
decalage = 1
ligne = 2
Do
    If ActiveSheet.Cells(ligne, 10).Value = "" Then Exit Do
    ligne_test = 2
    Do
        If ActiveSheet.Cells(ligne_test, 10).Value = "" Then Exit Do
        If ActiveSheet.Cells(ligne_test, 10).Value = ActiveSheet.Cells(ligne, 10).Value Then
            ActiveSheet.Cells(ligne, 11 + decalage).Value = ActiveSheet.Cells(ligne_test, 1).Value
            decalage = decalage + 1
        End If
        ligne_test = ligne_test + 1
    Loop
    decalage = 1
    ligne = ligne + 1
Loop
Application.ScreenUpdating = True
End Sub

Bonjour,

Avant même de creuser dans ton code ... tu peux jouer avec

Application.Calculation

en mode manuel pour démarrer, et en mode automatic à l'arrivée ...

Application.Calculation = xlCalculationManual
' tout ton code actuel
Application.Calculation = xlCalculationAutomatic

Ok, je teste en rajoutant ça

Bonsoir,

Une autre remarque purement VBA :

vous déclarez les variables ligne et ligne_test en "Integer"

Dim ligne As Integer
Dim ligne_test As Integer

Sachez que cette assignation permet de dire à VBA que ces variables sont codés sur 2 octets (je crois) et que donc leurs valeurs peuvent aller de -32000 (et des poussières) à 32000 (et des poussières aussi) en entier !

Donc si vous avez 100 000 lignes.... forcément VBA ne peut pas le comprendre...

Pour les références de lignes ou colonnes il vaut mieux définir en "Long"

Dim ligne As Long
Dim ligne_test As Long

Ensuite, pour le temps que met la macro...

@ bientôt

LouReeD

Bonsoir, enfin re

Je me suis permis de réfléchir sur votre macro...

L'idée de travailler sur des "collections" permettra peut-être de travailler plus vite.

Donc on met en collection la plage de la colonne I, puis on remplace les "For to Next" par du "Each" qui permet de scruter une collection.

A vous maintenant de voir si cela tourne plus vite.

Une correction du script est faite sur le numéro de colonne par rapport à un fichier précédent : I est en 9 et non pas en 10

Ci-joint le fichier avec la nouvelle macro.

@ bientôt

LouReeD

Re

je viens de faire un teste avec 147000 ligne et ma foie j'ai du faire un [esc] pour stopper la macro... trop long !

En plus je vois que seule la première ligne est remplie ! Et sur cette première ligne, beaucoup de références se répètent !

En plus de tester verticalement, il faudrait vérifier le non doublon horizontalement...

l'idée pour ceux qui connaissent bien le VBA :

Un premier filtre sur la colonne I donne le nombre de références concaténées uniques.

ensuite il faudrait pouvoir (à tour de rôle) filtrer le tableau sur ces références concaténées uniques.

Cela nous donne les références uniques en colonne A, qui chacune devraient être copiées en face de chaque ligne du filtre ci-dessus.

A la main cela marche bien, il suffirait de retranscrire en VBA

@ bientôt

LouReeD

Bonjour

Un essaie

2 passages sur la source.

Un premier en lecture

Puis en écriture.

Utilisation d'un vecteur dynamique dans un vecteur dynamique.

Je n'ai pas testé au niveau des performances.

A mon avis, une indexation par TRI serait encore plus performante.

Bonjour,

je viens de tester, enfin je suis en train de tester sur 147000 lignes mais c'est trop long...

N'y a t-il pas moyen de gérer les possibilité de filtre d'un tableau ? car je pense que c'est la solution la plus rapide.

@ bientôt

LouReeD

Merci beaucoup pour le temps passé.

A tester

Avec Tri et indexation.

Bonjour,

moi je l'ai testé ce matin... trop long...

@ bientôt

LouReeD

Bonjour,

et si tu expliquais concrètement ce que tu veux plutôt que de nous laisser deviner en épluchant ton code ?

eric

LouReeD a écrit :

Bonjour,

moi je l'ai testé ce matin... trop long...

@ bientôt

LouReeD

Trop long, ca ne veut pas dire grand chose.

Vous avez comparer les temps des 3 ?

Il peut être tout à fait logique que ce soit long pour un calcul complexe.

Bonjour à tous,

j'ai réussi à avoir un résultat assez rapide en utilisant un filtre avancée.

Mon critère 1 a seulement 20 valeurs différentes, du coup j'ai filtré avec la première valeur en demandant de copier le résultat dans la feuille 2 puis j'ai filtré avec la seconde valeur en demandant de copier le résultat dans feuille 3... Puis je fais éxecuter la macro de Loureed sur chaque feuille et ensuite je réassemble toutes les feuilles dans la première. Cette solution fonctionne en pratique mais c'est loin d'être une solution élégante.

une autre idée : est de trier la colonne concatener et de faire une macro qui compare la ligne 1 avec la ligne 2, si les deux lignes sont égales, ils écris le résultat et compare la ligne 1 avec la ligne 3 si la ligne 3 et différentes, il compares la ligne 3 avec la ligne 4 (je vais écrire mon autre idée pour être plus claire).

et si tu expliquais concrètement ce que tu veux plutôt que de nous laisser deviner en épluchant ton code ?

bis repetita

eriiic a écrit :

et si tu expliquais concrètement ce que tu veux plutôt que de nous laisser deviner en épluchant ton code ?

bis repetita

J'ai soudainement l'impression de perdre mon temps!

Bonsoir à tous

Trop long pour moi c'est quand j'ai le temps de me raser, de prendre une douche,d' aller à la boulangerie et de revenir et de voir que la macro tourne encore...

Ceci dit il n'y avait pas 100 000 lignes mais 147000 lignes soit prés de 50% de plus.

Ce qui me chiffonne c'est qu'un filtre sur la colonne I nous permets de sélectionner une valeur "concatener" unique et en colonne A on a le choix entre les différentes références s'y référant...

A priori après recherche sur le "net" il n'est pas possible de connaître les valeurs de tri automatique d'une colonne d'un tableau excel... dommage, car un simple copier coller des filtre possible en colonne A suite au filtre de la colonne I permettrai d'avoir ce que Hydra cherche à faire...

@ bientôt

LouReeD

Bonjour

Moi aussi j'ai cherché

Une question qui se posait c'était de savoir ce qu'il fallait marquer dans les colonnes J à ........

En suivant ton code j'ai compris qu'il fallait toutes les valeurs de la colonne A même si dans cette colonne il y avait des doublons

En dupliquant les lignes pour avoir un fichier conséquent j'aurai obtenu plus de 16384 valeurs, ce qui dépassent la capacité en colonne d'une feuille Excel

Alors n'ayant pas trop envie de construire un fichier j'aimerais savoir si tu as construit un fichier de telle manière que le maximum de données ne dépasse pas 16 000 colonnes

Et dans l'affirmative si tu peux poster ton fichier

Merci par avance

Bonjour,

Ce n'est pas ma demande, mais si je comprend bien dans les colonnes J à .... il faut inscrire les référence de la colonne A qui ont la même valeur concaténée dans la colonne I, et l'idéale serait qu'il n'y ai pas de répétition, donc on irai pas au delà des 16000 colonnes à mon avis. Hors à présent on ne teste pas cette répétition de doublon donc on dépasse les capacité d'Excel en effet.

Comme je le dit, en filtrant sur la première valeur de la colonne I, il ne reste que 5 valeurs de filtre sur la colonne A.

Comment récupérer ces 5 valeurs afin de les copier sur les lignes filtrées suite au filtre de la colonne I, La solution est là mais au vu des surfs il semblerai qu'il ne soit pas possible de "boucler" sur de telles valeurs? On peut en VBA demander un filtre auto sur une valeur connue, mais on ne peut pas extraire les valeurs possibles de filtre...

A moins que quelqu'un sache le faire....

@ bientôt

LouReeD

Bonjour

Comme tu l'as suggéré dans un de tes précédents messages, je passe par un filtre

Mais je ne peux le tester sur un fichier "conséquent"

A vérifier