Copier -> collage special valeur trop long VBA Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
b
bipbip8086
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 11 mai 2016
Version d'Excel : 2007

Message par bipbip8086 » 12 mai 2016, 21:32

Bonsoir à tous,

J'ai une question concernant le copier/collage special valeur...

Pour faire simple j'ai réalisé une macro permettant de copier environ 7000 lignes contenant des recherchev et autres formules pour ne travailler qu'avec les valeurs... cependant cela prend beaucoup de temps :(

La partie du code en question est le suivant:

rows("20:7000").select
selection.copy
range("A20").select
ActiveSheet.PasteSpecial Paste:=xlPasteValues

C'est quand la macro arrive sur la partie collage special que cela prend beaucoup de temps...
En gros je copie les cellules, et je les colle au même endroit... mais en valeur

Y a t il un moyen d'optimiser cela?

Merci les amis :)
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'340
Appréciations reçues : 158
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 12 mai 2016, 21:47

Bonsoir
Si tu peux arriver à déterminer la dernière colonne utile, par exemple DX
tu fais comme ça
Sub test()
Dim a()
a = Range("A20:DX7000")
Range("A20:DX7000") = a
End Sub
A+
Avatar du membre
waard
Contributeur
Contributeur
Messages : 436
Appréciations reçues : 16
Inscrit le : 9 février 2015
Version d'Excel : 2013, 2016, FR
Téléchargements : Mes applications

Message par waard » 12 mai 2016, 22:34

Hello, :)

En utilisant des plages cells et en cherchant la dernière colonne de chaque ligne, on peux déjà limiter la plage à copier et en utilisant la désactivation de l'affichage et du calcul auto, ça doit accélérer, à voir si mon idée peux convenir :
Sub boucleligne()

Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
Application.ScreenUpdating = False 'désactive l'écran
ligne = Range("A2").Value 'ligne de départ dans a2

For i = 1 To Range("C2").Value 'Boucle de 1 jusqu'à la valeur indiqué dans c2
fincolligne = Cells(ligne, Cells.Columns.Count).End(xlToLeft).Column 'avoir la fin de la ligne
'Range(Cells(ligne, 1), Cells(ligne, fincolligne)).Interior.ColorIndex = 8
Range(Cells(ligne, 1), Cells(ligne, fincolligne)).Copy 'copier selection ligne en cours définit à chaque tour de boucle
Range(Cells(ligne, 1), Cells(ligne, fincolligne)).PasteSpecial xlPasteValues
ligne = ligne + 1 '+1 pour passage boucle suivante la ligne prendra +1 pour simuler le déplacement
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "fin du copier/coller"

End Sub
;;)
Classeur1.xlsm
(22.6 Kio) Téléchargé 27 fois
"One and yet a mass"
COHENNY Tristan - Projet MYRMIDON
b
bipbip8086
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 11 mai 2016
Version d'Excel : 2007

Message par bipbip8086 » 12 mai 2016, 22:43

Bon il y a un truc que je n'arrive pas à expliquer!

J'ai testé la solution de galopin01 en mode "manuel" (F8 sous VBE)

Ca marche super bien, environ 1min pour réaliser ma macro :wink:

Par contre, dès que je génère la macro par le biais de mon "bouton", donc en mode "auto", cela prend plus de 10min!!

Je ne comprend pas d'où provient ce problème :(

Bug de la macro?
Avatar du membre
waard
Contributeur
Contributeur
Messages : 436
Appréciations reçues : 16
Inscrit le : 9 février 2015
Version d'Excel : 2013, 2016, FR
Téléchargements : Mes applications

Message par waard » 12 mai 2016, 23:01

ça doit être le temps de traitement sur la plage, j'ai testé en mixant ma boucle par ligne avec la méthode de galopin01 et ça le contourne apparemment :

Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
Application.ScreenUpdating = False 'désactive l'écran
ligne = Range("A2").Value 'ligne de départ dans a2

For i = 1 To Range("C2").Value 'Boucle de 1 jusqu'à la valeur indiqué dans c2
fincolligne = Cells(ligne, Cells.Columns.Count).End(xlToLeft).Column 'avoir la fin de la ligne
'Range(Cells(ligne, 1), Cells(ligne, fincolligne)).Interior.ColorIndex = 8
a = Range(Cells(ligne, 1), Cells(ligne, fincolligne)) 'copier selection ligne en cours définit à chaque tour de boucle
Range(Cells(ligne, 1), Cells(ligne, fincolligne)) = a
ligne = ligne + 1 '+1 pour passage boucle suivante la ligne prendra +1 pour simuler le déplacement
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "fin du copier/coller"

End Sub

vive la méthode à galopin01 :clap:
"One and yet a mass"
COHENNY Tristan - Projet MYRMIDON
b
bipbip8086
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 11 mai 2016
Version d'Excel : 2007

Message par bipbip8086 » 13 mai 2016, 11:26

Bien le bonjour à vous :) ,

Bon j'ai testé les 2 solutions proposées :wink:

La solution de galopin01 est plus rapide que celle de waard (dans mon cas)

Par contre je n'arrive toujours pas à comprendre pourquoi ca prend autant de temps en mode "auto" par rapport au mode "manu".. je ne vois vraiment pas ce qui peut bloquer à ce point

J'ai copier coller la macro, refait mon bouton générer, enregistrer le fichier sous un autre nom... rien y fait

Je crois bien que cela va rester comme ça et puis c'est tout :|
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'340
Appréciations reçues : 158
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 13 mai 2016, 12:03

Bonjour,
Tu n'aurais pas de macro Worksheet_Change par hasard ?
Dans ce cas un petit coup de
Application.EnableEvents = False

avant puis
Application.EnableEvents = True 
après...
A+
b
bipbip8086
Jeune membre
Jeune membre
Messages : 10
Inscrit le : 11 mai 2016
Version d'Excel : 2007

Message par bipbip8086 » 18 mai 2016, 08:04

Bonjour à vous,

Pas de Worksheet_Change qui traine.

J'ai utilisé la 1ere solution à galopin01, qui me permet de gagner un peu de temps tout de même :)

Merci à tous pour vos idées :wink:
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message