Discussion :

[Pierre Fauconnier]

NVCfrm,

Je suis d'accord que la manip sur filtre automatique sera plus rapide, mais elle ne s'intègre pas dans un processus VBA global. J'imagine mal, lors d'un traitement global, le code s'arrêter et envoyer un popup "Merci de supprimer à la main puis de lancer la suite du code"...

Donc parfois, même si la solution paraît très simple et rapide via l'interface Excel, la demande globale impose qu'on la fasse en VBA. Et dans ce cas, il y a des solutions moins catastrophiques qu'un filtre avancé avec export et suppression de la feuille qui ne me semble pas du tout réaliste. Par contre, singer en VBA les manips dont tu parles au niveau Excel me semble être une piste intéressante si le besoin existe d'optimiser

[retraite83]

Et si un jour je dois modifier cela ?

je vais mettre plus de 2mn...
Je laisse cette solution aux experts
merci quand même

[Pierre Fauconnier]

Code :

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

n = .Sheets(1).Name: Application.DisplayAlerts = False: .Sheets(1).Delete: .Sheets(2).Name = n

Si des formules pointent vers sheets(1), je te souhaite bon courage pour la suite... Ce n'est pas une solution générique, fiable et professionnelle. Et ce n'est pas toi que je mets en cause, c'est juste la solution que je critique.

[NVCfrm]

NVCfrm,

Je suis d'accord que la manip sur filtre automatique sera plus rapide, mais elle ne s'intègre pas dans un processus VBA global. J'imagine mal, lors d'un traitement global, le code s'arrêter et envoyer un popup "Merci de supprimer à la main puis de lancer la suite du code"...

Donc parfois, même si la solution paraît très simple et rapide via l'interface Excel, la demande globale impose qu'on la fasse en VBA. Et dans ce cas, il y a des solutions moins catastrophiques qu'un filtre avancé avec export et suppression de la feuille qui ne me semble pas du tout réaliste. Par contre, singer en VBA les manips dont tu parles au niveau Excel me semble être une piste intéressante si le besoin existe d'optimiser

Bah à chaque champ de bataille ses propres moyens de combat. Sur le terrain VBA j'imagine qu'une ligne de code, au plus deux suffiront.

[Invité]

Comme j'ai dit tout à l'heure faut voir et je rejoins NVCfrm le contexte peut exiger une autre approche!

[Invité]

sur une page de A1:V1048576

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
Sub test()t = Time
With ThisWorkbook
    .Sheets(2).Range("A1") = "cham1": .Sheets(2).Range("A2") = "<>x"
FiltreActif .Sheets(1).UsedRange, .Sheets(2).UsedRange, .Sheets(3).Range("A1"), False
n = .Sheets(1).Name: Application.DisplayAlerts = False: .Sheets(1).Delete: .Sheets(2).Name = n
End With
Debug.Print (Time - t) / 100
End Sub
Private Function FiltreActif(RangeSource As Range, CriterRange As Range, CopyRange As Range, Optional Unique As Boolean = True) As Boolean
FiltreActif = False
On Error Resume Next
 RangeSource.AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=CriterRange _
        , CopyToRange:=CopyRange, Unique:=Unique
        DoEvents
        If Err = 0 Then FiltreActif = True
        'MsgBox Err.Description
        On Error GoTo 0
End Function

Pièce jointe 313231

comme dab pas de justification du
-1, perso j'en mets jamais!

[GESCOM2000]

Bonjour

Moi je mettrais tout dans a tab() puis traitement en boucle et après un apllication.transpose....

[Pierre Fauconnier]

comme dab pas de justification du
-1, perso j'en mets jamais!

Bah. Je m'en suis pris un sur chaque message de la discussion, alors que j'ai chaque fois justifié mes dires. Ne t'arrête pas à ça

Cela étant, il faut reconnaître que la solution que tu proposes pose des problèmes, et j'en ai soulevé un tout à l'heure, en mentionnant les conséquences fâcheuses de la suppression d'une feuille. J'y ajouterai qu'elle ne fonctionnera que si une deuxième feuille de calcul est présente, car tu utilises sheets(2). Il faut donc qu'il y ait une deuxième feuille ET que ce soit une feuille de calcul. Si pas de deuxième feuille ou que c'est une feuille de graphique, le code plantera... Tu as le même problème avec Sheets(3).

Comme autre conséquence, si sheets(2) et sheets(3) existent, voilà que tu massacres ce qui se trouve dessus pour mettre ta plage de critère et ta place de copie.

Et tu proposes tout cela à un débutant sans le mettre le garde. A ta place, je ne serais pas étonné de recevoir un -1... Tu en mérites 10 sur ce coup-là (et ce n'est pas une attaque personnelle, crois-le bien, c'est une simple constatation technique des dangers d'une solution que je persiste à considérer comme non professionnelle)

[NVCfrm]

Bah. Je m'en suis pris un sur chaque message de la discussion, alors que j'ai chaque fois justifié mes dires. Ne t'arrête pas à ça

Vos remarques m'ont mis la puce à l'oreille. Il y a effectivement un participant silencieux qui a voté sur presque tous les posts.

Ce qui à mon humble avis n'a pas de sens.

[Invité]

C'est moi mais dans une autre discutions ou en plus j'ai été tout simplement censuré j'avais prévenu!

Non seulement mais remarque était constructive, pertinente et argumentées!

[Marc-L]

Bonjour, bonjour !

Avis non pas d'expert mais juste un retour d'expérience …
Hier j'ai effacé mon post sur ce fil car Joe dans le post #6 avait déjà émis une idée proche.
Mais suite aux différentes remarques, je me dois de l'appuyer !

Au lieu d'offrir des solutions techniques à un niveau que souvent le demandeur ne pourra maintenir,
quand le B-A-BA d'Excel dispose déjà de tout le nécessaire pour solutionner le besoin exprimé
donc le demandeur pouvant déjà être à même de le réaliser manuellement - le cas ici - et dans un temps satisfaisant,
je préfère cette voie de débutant au lieu d'une autre dite experte.

En utilisant une colonne supplémentaire pour différencier les lignes à conserver de celles à supprimer
via une simple formule de calculs (souvent niveau débutant) retournant respectivement 0 ou 1 ou bien FAUX ou VRAI,
il suffit juste de trier la plage sur cette colonne, alors les lignes à supprimer sont regroupées en fin de tableau
et peuvent être donc supprimées en un bloc en une seule opération et donc sans recourir à une boucle lente !

Quand j'ai vu un stagiaire réaliser cette manipulation en moins d'une minute sur une grosse base de données
alors que l'usine à gaz d'un pompeux personnage effectuait la même procédure via une boucle en plus de dix minutes,
je me suis dit que la voie du stagiaire ne prendrait alors pas trente secondes par code
et qu'à l'avenir je devrais ne pas oublier de « Penser Excel avant VBA » !

Il y a déjà eu démonstration de cette simple voie dans des discussions de ce forum.

Et même si une méthode avancée / experte est plus efficace,
faut-il encore ne pas oublier la capacité du client à maintenir une telle procédure …

Et si nous ne travaillons pas pour GDF, évitons alors les productions gazières !

Voilà, voilà !

___________________________________________________________________________________________________________

Je suis Paris, Barcelone, London, Manchester, Egypte, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

[Pierre Fauconnier]

[INDENT=2][...]

Je ne peux que plussoier...

[RyuAutodidacte]

hi,

un de plus pour Marc, connaissant bien le sujet grâce à l'aide qui m'a été donné auparavant
Je rajouterai sur les conseils que tu as déjà donné sur le forum, préférer plutôt la méthode Clear plus rapide que la méthode Delete qui sera un peu plus lente

Edit : Voilà un Exemple que j'avais donné en vba (j'ai utilisé Delete mais on aurait bien pu mettre Clear)
https://www.developpez.net/forums/d1...e/#post9205474

[RyuAutodidacte]

Re,

J'essaye de vous suivre... mais je suis un peu loin derrière...
en clair pour mon fichier d'environ 5000 lignes comment supprimer les lignes qui ont un "X" dans une colonne particulière (la 39) en une seule fois...
Ma macro en boucle marche très bien mais en 2mn, si je peux améliorer c'est bien si ça reste lisible pour moi..

Vos discussions sont passionnantes, ou plutôt seront... c'est ma première macro... pensez-y..
Merci

Ce n'est qu'un exemple que j'ai donné et facilement adaptable lorsque vous serez un peu plus aisé en vba
(d'autres personnes qui ont le même type de question et qui lisent ce post, peuvent être intéressé par le lien).

La solution la plus simple a été donné par Marc à faire manuellement et sans une ligne de code, donc à la porté de tous !
Voir ce post

Donc si on prend la colonne 39 (Dans la fenêtre d'exécution (en vba) :

Code :

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

1
2
Debug.Print Columns(39).Address 'on écrit ce code et on valide avec la touche retour - cela évite de chercher à quoi correspond la colonne 39
' le résultat => $AM:$AM

En reprenant une simple formule que l'on mettra après la dernière colonne (donc colonne supplémentaire utilisé) :

Code :

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

=SI($AM1 = "X";1;0)

on pourra étendre la formule automatiquement en double cliquant sur le coin de la cellule en bas à droite
puis on sélectionne l'ensemble des colonnes y compris celle avec la formule permettant le tri
Faire alors dans Données => Trier (en indiquant si il y a une en-tête), choisir la colonne contenant la formule que l'on a étiré
et faire le tri dans l'ordre croissant
il suffit juste après de supprimer les lignes dont le résultat donné par la formule est 1 se trouvant ver le bas

Voilà

[NVCfrm]

Comme le disait Joe, c'est intéressant ces opinion diverses apportés par d'admirables contributeurs, élargissant l'éventail des choix aux lecteurs, qui pourront comprendre qu'il n'y a pas de solution unique à un problème informatique.

Tout est une question de contextes.

Par exemple, en lisant les premiers posts de cette discussion, je me suis demandé si Pierre faisait exprès, avant de considérer rapidement qu'il restait volontairement accroché à la solution d'origine du niveau du demandeur (nous le faisons tous très souvent au gré des contextes).
Il aurait pu selon ses inspirations à un autre moment, lui suggérer simplement de se passer de code quelconque comme je l'ai fait.

Je suis en principe avare en codes, je préfères souvent laisser entrevoir au demandeur des horizons à explorer par ses propres moyens que de lui donner une ligne de code qui risque de le rendre dépendant de moi.

S'il s'agit de la recherche de performances absolues pour des situations semblables mais aux contextes différents, ce n'est jamais gagné.

Pour conclure : Une méthode étonnante par ses performances dans toutes les situations connues où elle peut s'appliquer, n'est pas forcément la méthode indiquée à TOUTES les situations.

_________________________________________________

Cette discussion étant devenu un débat autour de solutions appropriées, (je rappelle le titre : Performance macros sous Excel. Digne d'un tuto)

Dans certaines situations, la création d'une colonne intermédiaire avec les valeurs de tri peut-être aussi très coûteux en performances. Sans parler du tri qui peut mobiliser également beaucoup de ressources.
En fait le filtrage est beaucoup plus rapide que le tri dans cette situation.

RyuAutodidacte, est il est vrai que Clear est plus rapide que Delete ?
Pour arriver à cette conclusion, il faut soit l'avoir expérimenté, soit avoir une idée de la programmation de ce qui est derrière le rideau.

hi,
...
Je rajouterai sur les conseils que tu as déjà donné sur le forum, préférer plutôt la méthode Clear plus rapide que la méthode Delete qui sera un peu plus lente

Ce n'est pas tout à fait vrai. Je dirais même plus ce n'est pas vrai.
Je le répète tout dépendra des contextes.

[Marc-L]

Delete ligne par ligne via une boucle est bien plus long qu'un tri puis Clear d'un bloc de cellules.
(Vu ici et sur d'autres forums …)
Idem pour Delete d'une plage filtrée, plus elle est grande plus il y a de lignes isolées et plus c'est long !

Delete d'un bloc de lignes - donc après le tri - doit être un iota plus "long" à cause de la remontée des lignes suivantes …

[NVCfrm]

Delete ligne par ligne via une boucle est bien plus long qu'un tri puis Clear d'un bloc de cellules.
(Vu ici et sur d'autres forums …)
Idem pour Delete d'une plage filtrée, plus elle est grande plus il y a de lignes isolées et plus c'est long !

Delete d'un bloc de lignes - donc après le tri - doit être un iota plus "long" à cause de la remontée des lignes suivantes …

Comme tu le décris, c'est tout à fait juste. Encore faut-il le voir sous l'angle décrit Marc.

[NVCfrm]

Me voilà bien obligé de discourir malgré moi.

N'oublions pas que tout est une histoire de contextes déjà dit.

Dans tous les deux cas, nous nous interresserons peu aux processus déclenchés pour faire correspondre le graphisme aux nouvelles donnes.

A première vue en se representant le processus comme tu le fais on croirait que Delete agit individuellement sur les objets Range et que Clear le fait en un seul coup sur un groupe.

D'abord dans chaque cas il s'agit d'un objet chaîné. Et dans chaque cas les programmes Clear ou Delete agiront individuellement sur chaque objet, que ce soit en fournissant un groupe de données connnexes ou discontinus, les deux programmes feront la même chose : un parcours en chaîne.
La représentation continue ou discontinue de chacun de nos 2 objets (filtrés, triés) via l'interface graphique est abstraite. Sinon Excel sera pire qu'un escargot.

Maintenant à votre avis libérer la mémoire ou faire pointer une reférence à NULL et inititialiser des valeurs en cascade dans une référence, qu'est ce qui peut être rapide ?

Pour ce qui est du tri ou du filtre, je n'ai pas besoin de te demander d'expérimenter. Je te demandes juste de voir les 2 concepts.
Dans tous les deux cas un nouveau tableau est crée pour stocker le résultat.
Dans l'un, un algorithme complexe rentre en jeu pour déplacer les données à la bonne position du critère SUP ou INF qui n'est définitivement acquis qu'après avoir pris 1 par 1 les élément à ordonnés( ce 1 par 1 que tu sembles croire ne pas se faire avec le tri). Dans une grande donnée bien mélangée la valeur du même rang peut être changé des centaines de fois.
Dans l'autre on se contente de faire suivre ou ignorer les éléments répondant au critère EQ.

Pour que vous puissiez me comprendre, oublier tout ce que vous montre Excel ou le VBA et descendez un dégré en dessous, pour imaginer (on n'a pas d'autre choix que d'imaginer en l'absence du code source) un peu comment vous concevriez ces 2 programmes au bas niveau.

Edit : je ne suis pas dans les conditions idéales. Excel et moi, particulièrement windows et moi commence à devenir de l'histoire ancienne.

Je propose d'essayer ce code avec de très grandes données avec votre code du tri, par exemple sur un tableau de 20 colonnes et 500000 lignes.

Code :

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

1
2
3
4
5
6
7
8
9
Dim r As Range
Set r = Range("A1").currentRegion.columns(1)
With r
   .AutoFilter 1, "x"
   If Application.Subtotal(3, r) > 1 Then
      .SpecialCells(xlCellTypeVisible).Range(Cells(2, 1), Cells(.End(xlDown).Row, 1)).EntireRow.Delete
   End If
   .Worksheet.AutoFilterMode = False
End With

N'oubliez pas de désactiver le rafraîchissement au bont moment et ne le réactiver qu'après soustraction du temps.
Je suis curieux de savoir ce que ça peut donner.

[Pierre Fauconnier]

Je rappelle juste que la première obligation d'un développement, c'est qu'il fasse ce qu'on lui demande. Après, si on veut, on optimise. Passer des heures à optimiser un truc qui prendra une seconde plutôt que deux minutes, c'est rentable sur le (très) long terme.

Je rappelle le calcul: si on réalise l'opération une fois chaque semaine, une heure de programmation pour gagner deux minutes sera rentable en trente semaines... Avant cela, ça coûtera des sous... j'ai mis des années à le comprendre, et durant ce temps, j'ai développé gratis...

[retraite83]

Je partage l'avis de Pierre Fauconnier, ma fille est ravie d'avoir ce petit outil qui lui permet de préparer son document en 2 minutes alors qu'il lui fallait un temps fou avec word...
En passant, pour un néophyte comme moi je trouve paradoxal d'avoir à utiliser Excel pour préparer un document à imprimer, mais bon, première surprise...

Concernant ma question de départ, le temps d'exécution de la macro (7 mn, c'était un peu long quand même), puis-je suggérer une réflexion...

Tout d'abord vos discussions entre experts est certes surement passionnante, mais reconnaissez qu'elles sont loin de la compétence du commun des mortels.

Seconde surprise, je découvre que Monsieur l'ordinateur et son langage VBA commence par la fin du document à cause de sa logique personnelle et fait cela surement quelques centaines de fois pour supprimer les lignes qui ont juste un "X" dans une colonne, d'où 2 mn, un temps qui me conviens mais surement très long vu la puissance de ces machines... Entourés d'autant d'informatique dans notre vie quotidienne, je commence à comprendre pourquoi tout va à l'envers dans notre monde (mais cela n'engage que moi)...

Ne peut-on pas demander à VBA, hyperpuissant et sophistiqué, de travailler "normalement" en commençant parle début du document et faire les suppressions "normalement" en une seule fois.
Par exemple dans la macro trouver une solution pour supprimer la ligne TANT QUE le "X" est présent dans la colonne et ne faire +1 sur i que lorsqu'il n'y a pas de "X" même s'il faut tout ré-écrire ???

voilà ma petite macro

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub supprlinean()
Dim i As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 5000 To 1 Step -1
If Cells(i, 40).Value = "X" Then
Cells(i, 40).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub