Performance macros sous excel 2016

Version imprimable

Voir 40 message(s) de cette discussion en une page

20/09/2017, 11h33
retraite83

Performance macros sous excel 2016
Bonjour,

j'ai écrit une petite macro (ma première !) pour supprimer les lignes dans une feuille excel 2016 ayant un X en colonne AN (39)

ma macro marche très bien mais il faut plus de 7 minutes pour qu'elle s'exécute, il y a 5000 lignes mais cela me semble très long, y-a-t-il un moyen de faire mieux ?
Merci
Code:

1 2 3 4 5 6 7 8 Sub supprlineam() Dim i As Integer For i = 5000 To 1 Step -1 If Cells(i, 39).Value = "X" Then Cells(i, 39).EntireRow.Delete End If Next i End Sub
20/09/2017, 11h56
Pierre Fauconnier
Salut.

Ajouter en début de macro
Code:

1 2 application.screenupdating = false application.calculation = xlcalculationmanual
et en fin de macro
Code:

1 2 application.screenupdating = true application.calculation = xlcalculationautomatic
Ca devrait déjà aller beaucoup plus vite, car le rafraîchissement d'écran prend du temps, et le calcul automatique aussi.
Idéalement, il faudrait ajouter une gestion d'erreur, mais teste déjà si ta macro est plus performante ainsi.

oui merci cela va déjà beaucoup plus vite, 2 mn au lieu de 7, c'est parfait

ai-je mis les lignes au bon endroit ?

Code:

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

20/09/2017, 12h29
Pierre Fauconnier

D'habitude, on les met après les Dim... mais ça n'a guère d'importance ici.

2 minutes, c'est encore un peu long. Si le traitement ne doit pas être effectué trop souvent (à l'appréciation de l'utilisateur), c'est ok. Si ce délai devient pénalisant, on pourrait trier sur la colonne qui contient X, repérer le premier X, les compter, et supprimer en une seule passe. Ce serait alors quasi instantané.

NB: Merci de penser aux balises de code...
20/09/2017, 13h29
retraite83

Merci, 2 mn ça peut convenir d'autant que je ne peux pas trier sur les X puisque le but est de supprimer les lignes qui ont ce X en gardant la séquence des lignes
je vais mettre les lignes après le DIM
20/09/2017, 13h33
joe.levrai

Bonjour,

en ajoutant une colonne qui numérote tes lignes, tu peux ensuite trier la colonne qui contient les "X", faire la suppression d'un seul bloc, et retrier tes lignes sur la colonne de numérotation pour retrouver l'agencement initial ;)
20/09/2017, 13h37
retraite83

Oui bonne idée,
je vais tester et prendre la meilleure solution
je ferme la discussion comme résolue
20/09/2017, 15h25
NVCfrm

Citation:

Envoyé par retraite83

Merci, 2 mn ça peut convenir d'autant que je ne peux pas trier sur les X puisque le but est de supprimer les lignes qui ont ce X en gardant la séquence des lignes
je vais mettre les lignes après le DIM

2 mn, rien que pour 5000 lignes à traiter, si le pc n'est pas en cause, la méthode l'est.

Petite piste avec l'outil Filtre : via interface graphique, il est possible de réalise l'opération en quelques secondes. Via macro en 1 seconde ou moins.
20/09/2017, 15h29
Invité

Bonjour,
sur des feuille de cette taille, j’applique un filtre actif ou élabore <>"x" vers une nouvelle feuille! ensuite je supprime la feuille et je renomme la nouvelle feuille avec l’ancien nom!
20/09/2017, 15h36
joe.levrai

Je :plusser: le filtre avancé avec paramètre d'export !
20/09/2017, 16h06
retraite83

Merci de toutes vos remarques d'expert, mais c'est ma première macro ! un peu du chinois pour moi, déjà faire cette macro n'a pas été évident, rappelez vous la 1ere que vous avez écrite!
Alors sans plus d'aide je vais me contenter de 2 mn
Merci à tous quand même
20/09/2017, 16h21
Invité

http://philippetulliez.developpez.co...dvancedfilter/
20/09/2017, 16h34
Pierre Fauconnier

Citation:

Envoyé par dysorthographie

Bonjour,
sur des feuille de cette taille, j’applique un filtre actif ou élabore <>"x" vers une nouvelle feuille! ensuite je supprime la feuille et je renomme la nouvelle feuille avec l’ancien nom!

Je doute que ces manipulations dans Excel prennent moins de deux minutes... ;)

Après, on peut le faire en VBA mais honnêtement, devoir créer la plage de critères, copier les entêtes sur une autre feuille, extraire, supprimer (sans tenir compte que de façon générique, on peut avoir autre chose sur la feuille, bouton, tableau décalé par rapport à a1, ...), renommer... Ca me semble prendre la Grosse Bertha pour tuer une mouche, non? ;)

Si vraiment on veut aller plus vite, ma solution par tri sur les x couplée à celle de Joe pour retrouver l'ordre après suppression me semble un peu moins tordue et un peu plus accessible... ;)

Je rejoins assez notre ami retraité sur la sagesse de ses propos. Ca fonctionne, ca ne va pas aussi vite que ça pourrait mais c'est ok pour l'utilisateur? Nickel, il n'y a rien à changer. ;)

Avec un traitement qui passerait de 2 minutes à 1 seconde, et si son traitement est réalisé une fois par semaine et qu'il passe une heure à essayer de mettre en place les solutions plus rapides proposées, il ne sera gagnant que dans 30 semaines... S'il passe deux heures, il lui faudra plus d'un an pour rentabiliser son investissement... Restons pragmatiques... ;)
20/09/2017, 16h38
Invité

bonjour Pierre,
faut voire!
20/09/2017, 16h43
joe.levrai

J'utilise très souvent l'export d'une table avec filtre avancé et suppression de l'ancienne feuille.
Le temps est relativement bref

un sujet où j'illustrais la mécanique avec création (et suppression) de la zone de critère : https://www.developpez.net/forums/d1...s/#post8499166
20/09/2017, 16h56
retraite83

Je rejoins l'analyse de Pierre Fauconnier, ma fille sera enchantée d'avoir ce petit logiciel qui va lui permettre de passer une commande en un rien de temps (une petite demi-heure au lieu d'une 1/2 journée avec du word bidouillé à la main à chaque fois en ce moment)
Alors 2 mn de plus... Ne soyons pas plus royaliste que le roi...
Merci à tous
20/09/2017, 16h57
Pierre Fauconnier

Ca va devenir une discussion d'experts à la fin de laquelle personne n'aura convaincu personne.

Si les données source sont dans une table de données, il faudra la renommer. Si la feuille a un codename utilisé dans du code, il faudra lui réattribuer...

Et si des formules pointent vers la feuille que vous supprimez, vous faites quoi? Parce qu'un RECHERCHEV, par exemple, qui pointerait vers une table créée sur cette feuille se prendrait un beau petit #REF!, non?

Je veux bien, à la rigueur, envisager un export, une vidange de la table et un réimport (avec des tables de données, pas des plages simples), mais une suppression de la feuille, j'attends de voir les dégâts collatéraux...

Ca ne me paraît donc pas très réaliste de supprimer la feuille après l'export et de simplement renommer la nouvelle feuille comme l'ancienne... Mais bon, je veux bien vous croire quand vous dites que vous faites cela tous les jours... ;)
20/09/2017, 16h59
NVCfrm

Juste, il est vrai que les perfomances des manips sur l'interface ou via code dépendent toujours de la méthode e,ployée.

Selectionner les donnée de la colonne cible SHIFT + DOWN ou clic sur l'en tête de la lettre de colonne, clic sur outil filtre, clic sur la fléchette pour dérouler, filtre textuel est égal à x et hop, selection du resultat filtré et CTRL + - .
Selectionner une cellule contenant le critère, clic droit, filtrer, combinaison CTRL plus - et hop.

la réalisation ne prendra pas le temps de lecture de la description de la méthode, je crois.
20/09/2017, 17h00
joe.levrai

Tu as raison, tout dépend du contexte, du projet, des dépendances, des liaisons.

Au moins on a encore une fois le mérite de présenter des pistes diverses et variées :D

1 pièce(s) jointe(s)

sur une page de A1:V1048576

Code:

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

Voir 40 message(s) de cette discussion en une page