Discussion :

[retraite83]

Bonjour,

je me suis attaqué à un problème dépassant mes compétences, à savoir supprimer en une seule fois toutes les lignes d'une feuille contenant un "X" en colonne "AN" '(colonne 40)
Les données utiles sont de la colonne "B" à la colonne "AL" mais c'est bien la ligne que je veux supprimer

voilà mon code

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Set pl = [AN1].Resize(Cells(Rows.Count, 40).End(xlUp).Row)
    pl.Value = pl.Value
Columns(40).Replace What:="X", Replacement:="=na()", LookAt:=xlWhole
On Error GoTo fin
Set pl = Intersect(Columns("B:AL"), Columns(40).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow)
For Each shp In ActiveSheet.Shapes
    If Not Intersect(pl, shp.TopLeftCell.MergeArea(1)) Is Nothing Then shp.Delete: nb = nb + 1
Next
pl.EntireRow.Delete
fin:
Set pl = Nothing

Il fonctionne bien dans la majorité des cas sur différents PC, mais il dure très longtemps sur un PC.
Après avoir fait multiples tests et installés des timer je constate que lorsqu'il y a un temps d'exécution très long cela se passe sur la ligne

Code :

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

pl.EntireRow.Delete

de l'ordre de 30 mn... contre 2 secondes en temps normal

Je n'ai aucune idée du pourquoi, mais vous pouvez surement m'aider à optimiser ce bout de code pour essayer de résoudre le pb

merci d'avance

[MarcelG]

Bonjour retraite83, Bonjour au Forum,

Le plus simple, et le plus performant, serait de filtrer un tableau structuré (table de données) sur l'occurrence dont on souhaite supprimer les lignes.
Ensuite, la suppression du corps de cellules du tableau ainsi filtré ne poserait pas de problème.

Voir la proposition de Pierre précédant la-mienne dans cette discussion

Une variante personnelle consisterait à tester la présence de cette occurrence pour enchaîner cette suppression, en lieu et place de la gestion d'erreur.

Code :

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

1
2
3
4
Dim eq As Long
eq = Evaluate("=IFERROR(MATCH(""" & loccurence & """,T_Famille[Parent],0),0)")
 
If eq > 0

[retraite83]

Bonjour

le problème est que la feuille est une mise en page en vue d'éditer un PDF, du coup il y a de tout dans cette feuille, des titres, des photos, du texte, je ne vois pas comment structurer cela dans un tableau

Ce que je ne comprends pas c'est pourquoi cette séquence dure 2 à 3 secondes sur mon PC (un millier de lignes à traiter) et dure 30 mn sur un autre PC

[MarcelG]

Salut,

le problème est que la feuille est une mise en page en vue d'éditer un PDF, du coup il y a de tout dans cette feuille, des titres, des photos, du texte, je ne vois pas comment structurer cela dans un tableau

Pour ma part, je ne lie jamais un "état" à un formulaire.
Pour une gestion facilitée, je préfère adopter le shéma
Formulaire <=> Tableau structuré (table de données) <=> Etat

Ce que je ne comprends pas c'est pourquoi cette séquence dure 2 à 3 secondes sur mon PC (un millier de lignes à traiter) et dure 30 mn sur un autre PC

Je rencontre ce problème dans mon entité.
Voir si d'autres applications ne viennent pas nuire à la performance d'exécution (Salesforce...). Eventuellement, voir le mode de calcul (automatique, manuel).
Peut-être aussi l'équipement...

[Pierre Fauconnier]

Salut.

Personnellement, je fais comme Marcel. Je prépare mes données AVANT d'établir un rapport. Je peux ainsi profiter de tous les outils d'Excel pour la gestion et le traitement des données, le rapport n'arrivant que lorsque tout est nickel niveau données

[retraite83]

Bonjour Pierre

je comprends, en fait c'est un peu ce que je fais, j'ai une feuille "modèle" dans laquelle les formules Excel sont complétées par des traitement macros.

Le but est d'éditer un PDF variable en fonction des besoins utilisateurs

Dans ma feuille "modèle" j'ai consacré une colonne ("AN") pour mettre un "X" (par formule et par macros) dans les lignes à ne pas intégrer dans mon PDF

Quand tout est prêt et juste avant d'éditer le PDF je veux supprimer toutes lignes ayant ce "X" en colonne "AN" en conservant l'ordre des lignes restantes

Et c'est là que sur un PC c'est interminable alors que tout va bien sur le mien

Vos réponses m'oriente vers un pb du PC, mais n'étant pas expert PC je me pose la question soit d'optimiser mon bout de code, soit de trouver une autre astuce

[retraite83]

Bonjour,

pour ceux qui aiment les énigmes, mon code du post #1 :

sur les PC avec Excel 2016:
met environ 2 secondes sur 1400 lignes

avec les PC avec Office 365
met entre 25 et 30mn sur le même volume

en pas à pas je constate que tout le temps est consommé sur la ligne

Code :

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

pl.EntireRow.Delete

Tests faits une dizaine fois sur chaque type de PC

[Pierre Fauconnier]

Salut.

Observes-tu le même écart si tu places Application.ScreenUpdating = False en début de macro?

[retraite83]

bonsoir

il y a en début de macro

Code :

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

Application.ScreenUpdating = False

J'ai fait un dernier essai :

- j'ai créé un fichier de test minimum sur un PC en Excel 2016 : temps d'exécution de la ligne 2 secondes

- j'ai désinstallé Excel 2016 et installé Office 365 sur ce PC (puisque c'est la cible)

-J'ai exécuté à nouveau la macro du même fichier : temps d'exécution de la ligne : 14 minutes

Ce qui est étrange aussi c'est que cela ne plante pas, seulement très long

[rvtoulon]

Bonsoir à tous,
Dans la même idée que Pierre fauconnier, j'ai eu un problème de ce genre sur une macro qui impactait des calculs sur la feuille et sur d'autres feuilles...
Du coup en plus du ScreenUpdating, j'enlève le calcule automatique et le met en manuel en début de macro:

Code :

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

1
2
3
4
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

et à la fin:

Code :

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

1
2
3
4
With Application    
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

[retraite83]

Bonsoir,

oui j'ai pensé à ça; cela ne change rien

[retraite83]

Bonjour

je confirme un fonctionnement différent de la séquence de code entre Excel 2016 et Office 365

La séquence fonctionne sur TOUS les PC équipés d'Excel 2016

La séquence ne fonctionne sur AUCUN PC équipés d'Office 365

j'ai résolu le pb en changeant la façon de faire (un autre code)