Discussion :

[Clementitho]

Bonjour à tous,

J’ai besoin de votre aide concernant une macro. J’ai réussi tant bien que mal à réaliser une macro qui effectue toutes les tâches que je souhaitait en parcourant divers forums et en m’aidant malheureusement trop souvent de l’enregistreur de macro.

Néanmoins, dès que je lance ma macro sur des fichiers avec un nombre de lignes important, le temps d’exécution est trop long, étant donné que mon code est très loin d’être optimal. A titre d’exemple, mon code tourne pendant 6/7 mins sur 50 000 lignes.

A l’aide de l’exécution pas à pas, j’ai essayé de repérer les parties de mon code qui posaient problèmes.

Voici les principales parties que je souhaiterais optimiser :

Je me suis basé sur l’enregistreur de macro pour réaliser cette partie qui fonctionne comme je le souhaite mais qui prend trop de temps, je ne vois comment pas trop comment l’optimiser.

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
'Modification ordre colonnes
    Columns("B:B").Cut
    Columns("F:F").Insert Shift:=xlToRight
 
    Columns("H:H").Cut
    Columns("F:F").Insert Shift:=xlToRight
 
    Columns("H:H").Cut
    Columns("G:G").Insert Shift:=xlToRight
 
    Columns("W:W").Cut
    Columns("U:U").Insert Shift:=xlToRight
 
    Columns("X:X").Cut
    Columns("W:W").Insert Shift:=xlToRight
 
    Columns("AA:AA").Cut
    Columns("Z:Z").Insert Shift:=xlToRight

J’ai également des difficultés concernant la mise en page. Il me semble que l’éxécution de ces parties de mon code prend du temps.

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
'Mise en forme
    Range("A1:AB1").Font.ColorIndex = 2
    Range("A1:AB1").Interior.ColorIndex = 11
    Range("A1:AB1").Font.Bold = True
    Range("A1:AB1").RowHeight = 45
    With Range("A1:AB1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

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
20
21
22
23
     'Centrer le texte + renvoyer à la ligne
    With Range("A:AB")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Range("A:AB")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Voila, j’espère que vous pourrez me permettre de modifier ces parties afin d’avoir un code plus propre et surtout plus RAPIDE !

Je suis à votre disposition si vous avez besoin de précisions.

Je vous remercie d’avance,

Clément

[Mr Poulpe]

Bonjour,

Essaie peut être :

Code :

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

1
2
Application.Screenupdating = false
application.calculation = xlcalculationmanual

au début du code et

Code :

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

1
2
application.screenupdating = true
application.calculation = xlcalculationautomatic

à la fin du code,

Poulpe

[Clementitho]

Bonjour,

J'avais déja ajouté cette partie avant mon code :

Code :

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

1
2
3
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Merci quand même,

Clément

[Daniel.C]

Bonjour,

L'exécution de la première partie de ton code est instantanée. Est-ce que tu n'aurais pas des shapes ou autres images qui pourraient ralentir le code ? Exécute :

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
20
21
22
Sub test()
'Modification ordre colonnes
    Deb = Timer
    Columns("B:B").Cut
    Columns("F:F").Insert Shift:=xlToRight
 
    Columns("H:H").Cut
    Columns("F:F").Insert Shift:=xlToRight
 
    Columns("H:H").Cut
    Columns("G:G").Insert Shift:=xlToRight
 
    Columns("W:W").Cut
    Columns("U:U").Insert Shift:=xlToRight
 
    Columns("X:X").Cut
    Columns("W:W").Insert Shift:=xlToRight
 
    Columns("AA:AA").Cut
    Columns("Z:Z").Insert Shift:=xlToRight
    MsgBox Timer - Deb
End Sub

et poste le temps indiqué par le MsgBox.
La seconde partie peut être légèrement optimisée, mais ça doit être sans importance sur le temps d'exécution :

Code :

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

1
2
3
4
5
6
7
'Mise en forme
    With Range("A1:AB1")
        .Font.ColorIndex = 2
        .Interior.ColorIndex = 11
        .Font.Bold = True
        .RowHeight = 45
    End With

En ce qui concerne la 3e partie, tu répètes la mise en forme de la plage A:AB; donc supprime celle qui est inutile. De plus, cette partie du code écrase la mise en forme que tu as faite précédemment. Tu peux donc le supprimer. Pour la 3e partie, mets :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
     'Centrer le texte + renvoyer à la ligne
    With Range("A:AB")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Encore une fois ça ne doit pas avoir une incidence notable sur le temps de traitement.

[Clementitho]

Bonjour,

Sur le fichier de départ, la modification des colonnes est instantannée mais sur le fichier déja traité la modification des colonnes prend 35 secs. Je ne comprend pas trop pourquoi ?

J'ai modifier les parties concernant la mise en page, ce qui permet un très léger gain de temps.

Je te remercie pour ton aide,

Clément

[Daniel.C]

mais sur le fichier déja traité la modification des colonnes prend 35 secs.

C'est quoi le traitement ? Est-ce que tu peux poster le code ou mettre un classeur en PJ ?

[Clementitho]

Je viens de remarquer que j'effectuais la modification de l'ordre des colonnes après la mise en page ce qui prennait beaucoup de temps.

En déplacant cette partie du code, le temps de traitement est passé de 65sec à moins de 20secs sur mon fichier Test de 13 000 lignes. Merci !!