J’ai un fichier excel avec
Colonne A à F : des données à partir de la ligne 3
Ce sont des données que je colle sur x lignes.
Colonne G à K : des formules que je voudrais copier
Le nombre de lignes que je colle change à chaque fois (je limite le nombre de lignes à 10000), je voudrais que les formules se trouvant sur la ligne 3 des colonnes G à K se copient et se collent jusqu’à la dernière ligne x
Par tatonnement je suis arrivé au code ci dessous mais il met pas mal de temps à faire l’opération.
Avez vous une idée pour savoir d’où vient le soucis ? Peut être pour améliorer la chose.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
a = Range(“A3”).End(xlDown).Row - 2
Range(“G3:K3”).Select
Selection.Copy
Range(“G4”).Select
Range(Selection, Selection.Offset(a, 0)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
If Union(Range(“A3:A10000”), Target).Address = Range(“A3:A10000”).Address Then
Application.ScreenUpdating = False
If Range(“A4”) = “” Then
Range(“G4:W10000”).ClearContents
Range(“A3”).Select
Exit Sub
End If
a = Range(“A3”).End(xlDown).Row
If Union(Range(“A2:A10000”), ActiveCell).Address = Range(“A2:A10000”).Address Then
Range(“G3:K3”).Select
Selection.AutoFill Destination:=Range(Cells(3, 10), Cells(a, 23))
End If
Range(“A3”).Select
Application.ScreenUpdating = True
Pourrais-tu donner un exemple.
Pour reproduire toute une colonne, c’est relativement simple. Par exemple :
De G1 à K1, il y a les formules
Baliser les colonnes G1 à K1 et ce, jusqu’à Gx Kx > Edition > recopier > En bas
et la formule et recopier.
Normalement, une simple opération de copie, c’est rapide.
Peux-tu préciser quelques points : a = Range(“A3”).End(xlDown).Row - 2
Que se passe-t-il si seule la ligne 3 contient des données
(cas que tu sembles accepter, puis qu’on a plus bas un If Range(“A4”) = “” Then) ?
A quoi servent les lignes de code à partir de If Union … ?
Cela ne semble pas faire partie de la copie. Dejà, en les invalidant, regarde si ce ne sont pas elles qui ajoutent de la lenteur
Si tu veux optimiser, évite de passer par des select et des ActiveCell, tu peux t’en sortir simplement avec un range().offset()
Merci pour vos messages.
Le fichier est prévu pour que les formules se recopient de la ligne 3 à la ligne X (environ 10000 lignes).
réponse à gcc : si seule la ligne 3 contient des données, ben le traitement devrait être “inexistant” car pas de copie/colle des formules sur les lignes suivantes. Par contre il faudrait que les formules des autres lignes soient effacées si l’utilisateur réutilise la même feuille de données.
Je vais voir en invalidant les lignes de code à partir de If Union …
réponse à Jacky67 : Comment fait on pour désactiver le recalcul automatique et le réactiver ???
Dans ce cas, ton programme se limite à 2 lignes :
Pour la recopie : if Range(“A4”) <> “” then Range(“G3:k3”).Copy Range(“A4”, Range(“A3”).End(xlDown)).Offset(0, 6)
Pour l’effacement, si les lignes sont inutilisées, on peut les effacer en entier. En se basant sur le principe qu’il y a au plus 1000 lignes utilisées et qu’une ligne inutilisée est une ligne qui n’a pas de données dans le colonne A : Range(“A1001”, Range(“A1001”).End(xlUp).Offset(1)).EntireRow.ClearContents
Bonjour
Jacky, ton code fait gagné un peu de temps mais je vias avouer que ce n’est pas encore ça !
gcc, ça n’a pas l’air de fonctionner le code suivant :
Range(“A1001”, Range(“A1001”).End(xlUp).Offset(1)).EntireRow.ClearContents
Ya une erreur de protection ?? et cellule en lecture seule ??? Comprends pas.
mais le principe de base est exacte.
Si tu ne gagnes (quasiment) pas de temps en désactivant le calcul automatique, c’est que le problème vient d’ailleurs.
Perso avec cette “astuce”, je suis passé de 30 minutes à 3 secondes pour la recopie de cellules.
Réduis la zone de nettoyage à celle que tu as donnée dans ton exemple Range(“G4:W10000”).ClearContents
pour voir si dans le principe ça fonctionne
Sinon, si tu as des cellules protégées, c’est normal que ça bloque
A ce moment là tu déprotèges juste avant le nettoyage ActiveSheet.Unprotect
Et tu reprotèges juste après ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Bien sûr, tu adaptes les arguments à ta propre situation
J’ai oublié de dire hier (même s’il est vrai que ça ne fera sans doute pas beaucoup avancer le Schmilblick) que le code me semble bien compliqué pour une simple copie de cellules.
Normalement il suffit d’un “copier” et d’un “coller” (ou éventuellement “insérer”).
A moins que je n’ai pas bien compris la question.
[edit]
Quant à la déprotection/reprotection, le code donné par Gcc est exactement le même que j’utilise depuis quelques jours et qui fonctionne à merveille chez moi.
[/edit]
Edité le 16/10/2009 à 17:04
Effectivement je déprotège puis protège la feuille par les memes arguments et ça fonctionne bien.
C’est en y mettant le code de gcc que l’erreur est venue et c’est ce que je comprends pas.
Effectivement, le truc c’est un copier puis coller. Le truc qui bloque un peu c’est qu’il faut que je puisse coller uniquement les lignes qui sont remplies et faire un clear pour les lignes d’en dessous jusqu’à la 10000 ème.
Voici donc le code utilisé :
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(“XXXX”).Unprotect Password:=“aaa”
Tu as toujours une erreur sur la ligne du clearcontents ?
1- Enlève les lignes
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
et leurs symétriques (tu les remettras après avoir trouvé l’erreur si tu constates qu’elles apportent de l’amélioration)
2- Décompose la ligne pour voir d’où vient le problème :
Range(“A10001”).End(xlUp).Offset(1).Select
Range(“A10001”,selection).Select
Selection.EntireRow.Select
Selection.ClearContents
3- Mets un point d’arrêt sur la première ligne, et ensuite avance en pas à pas jusqu’à ce que l’erreur se produise
4- Donne la ligne fautive et surtout le message d’erreur exact
Aussi, j’ai fait juste un code pour copier “G3:K3” et coller en “G4:K4”. La macro tourne une bonne dizaine de secondes.
Je comprends pas le truc.
Je voulais voir si je ne me prenais pas trop la tête en voulant prendre des précautions en effaçant les lignes non utilisées.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range(“A4”) <> “” Then Range(“G3:K3”).Copy Range(“A4”, Range(“A3”).End(xlDown)).Offset(0, 6)
End Sub
Je vais laisser ce code, apparement cela fonctionne, c’est un peu longuet mais bon ça fonctionne.
Je vais peut être metrre un barre de progression. Tu sais comment ça fonctionne, … désolé j’abuse !!
Pour l’effacement, je vais faire en sorte de m’en passer !
Merci en tous les cas de ton aide et de ta patience gcc.
Plutôt que supprimer la ligne correspondant à l’effacement, remplace ClearContents par Select.
Comme ça, tu n’auras plus qu’à appuyer sur la touche suppression pour faire ton effacement.
Et si comme ça non plus ça ne fonctionne pas, c’est qu’il y a quelque chose de pas normal quelque part
Tu utilises quelles versions de windows et Excel ?
“dépersonnalisé” = fichier sans données personnelles ou confidentielles.
Tu l’upload chez un hébergeur gratuit genre megaupload.com et tu nous donnes le lien pour qu’on puisse le télécharger et ainsi voir exactement de quoi il s’agit.
