Fichier Excel très lourd et macro non optimisée

Question

Me revoilà pour un nouveau problème ! j'ai élaboré une macro en VBA qui me facilite grandement la tâche pour mon travail de gestion des stocks. A quoi sert cette macro ? A l'aide de Power Querry je génére toutes les configurations possible de chaque produit (et il y en a un paquet), puis je duplique les lignes de mon classeur n fois. En fonction des produit je duplique les lignes disons entre 10 et 20 fois. Cette étape de duplication est indispensable pour générer par la suite des csv compréhensible par mon logiciel de gestion de stock. Quel est mon problème ? c'est mon premier code en VBA, il fonctionne presque parfaitement seulement je gère de très gros fichier excel et donc cela prend énormément de temps à excel pour calculer. Je pense que le soucis vient plus de la structure du programme que ce que j'essaie d'accomplir. j'ai joint un mon fichier excel, après la boucle "For j = 1 to nbCopies..." j'ai mis en commentaire une boucle K, c'est cette boucle qui fait ralentir tous le programme. Elle cherche et sélectionne sur une plage de 17 825 792 cellules le nombre de cellule vide, et ce sur 17 pages. Pour mieux comprendre ce que fait la macro, je vous conseille d'exécuter la macro "MACRO_TEST_COPIE_N_FEUILLE" . svp soyer indulgent, comme je l'ai déjà dit, je me met tout juste au VBA et il y a très probablement des variable, et lignes inutile. Je pense également que la méthode utilisé dans certains cas n'est pas la plus rapide pour le PC. Merci à vous pour votre aide ps: mon fichier dépasse largement les 1.5MO, j'ai donc mit un fichier très allégé, pour l'exercice il est nécessaire de dupliquer des lignes pour arriver au moins jusqu'à la ligne 84000...

Ergotamine · Answer

Je tente. J'arrive a un temps de 14,5 secondes VS 16 à 17 secondes, donc différence assez faible. M'enfin ce qui me gênait le plus était la non clarté du code et le nombre de variables utilisées, au point de s'y perdre. J'ai voulu passer par un array mais je n'ai pas réussi car je maîtrise mal les dimensionnement XD &gt; 1D. Ci-contre votre code en version allegée qui me semble faire la même chose : Sub MACRO_TEST_COPIE_N_FEUILLE() Dim WS As Worksheet, LR_I%, LC_I%, LMAX%, L%, LR_F%, LC_F%, C%, F% Application.ScreenUpdating = False 'Désactive la mise à jour à l'écran Application.Calculation = xlCalculationManual 'Désactive le calcul Set WS = Worksheets(1) 'Défini WS Feuille 1 LR_I = WS.Cells(WS.Rows.Count, 3).End(xlUp).Row 'Dernière ligne Feuille 1 LC_I = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column 'Dernière colonne Feuille 1 LMAX = WS.Rows.Count / (LC_I - 1) 'Nombre maximum de ligne à transférer par feuille L = 1 Worksheets.Add After:=WS, Count:=Round(LR_I / LMAX) 'Ajoute feuille selon LR_I et LMAX For F = 2 To Worksheets.Count 'Pour chaque nouvelle feuille With Worksheets(F) 'Avec cette feuille WS.Range(WS.Cells(L, 1), WS.Cells(LMAX, LC_I)).Copy 'Copie la plage feuille 1 .Activate 'Active feuille (pour tri ultérieur) .&#91;A1&#93;.PasteSpecial xlPasteValues 'Colle en valeur L = LMAX + 1 'Incrémente L à première ligne non copiée de feuille 1 LMAX = L + LMAX 'Incrémente LMAX pour définir future plage de copie LR_F = .Cells(.Rows.Count, 3).End(xlUp).Row 'Dernière ligne nouvelle feuille LC_F = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Dernière colonne nouvelle feuille .Range(.Cells(1, LC_F), .Cells(LR_F, LC_F)).Copy 'Copie index colonne P For C = 1 To LC_I - 1 'Pour C de 1 à LC - 1 .Cells(LR_F * C - LR_F + 1, LC_F).Offset(0, 1).PasteSpecial xlPasteValues 'Colle en valeur l'index à la suite * nombre de colonne Next C .UsedRange.Sort &#91;Q1&#93;, xlAscending 'Tri la table End With Next F 'Passe feuille suivante Application.ScreenUpdating = True 'Active la mise à jour à l'écran Application.Calculation = xlCalculationAutomatic 'Active le calcul automatique End Sub Il y a tout de même une différence majeure : le nombre de feuilles créées. Dans votre code vous demandez à créer un nombre de feuilles égal au nombre de colonne de votre feuille 1, hors vous y répartissez des lignes et non des colonnes. Je ne vois donc pas l'intérêt. Je suis parti sur l'hypothèse qu'on divisait le nombre de lignes pleines de la feuille 1 par le nombre maximum de ligne d'une feuille pour trouver le nombre de feuilles à créer. Ce qui est plus simple, on travaille avec deux feuilles au lieu de 17 .. Autre différence : Je ne travaille que dans un boucle. Je créé les feuilles puis je travaille feuille l'un après l'autre. Ca évite les sauts entres feuilles qui peuvent ralentir l’exécution. Je ne suis pas certain que vous obtiendrez moins sauf peut être en passant par des array VBA, car le nombre de données à traiter est conséquent et les copiés/collés/tris sont des opérations assez lourdes touchant à la structure de la feuille. En espérant que vous compreniez ce code, je n'ai pas l'habitude de commenter. Cdlt...

Ergotamine · Answer

Je ne comprend toujours pas car votre code quand je l’exécute me créé 16 feuilles ... Or quand j’exécute votre code sur 84 000 lignes je n'ai que 2 feuilles remplies car pour moi vous dupliquez les lignes par le nombre de colonne. Donc si une feuille contient au maximum 1 048 576 lignes, alors on ne pourra avoir que 65 536 lignes par feuille de données originales (65 536*16 = 1 048 576). Donc je divise mon 84 000 par 65 536 ce qui me donne en arrondi supérieur 2 feuilles. J'ai l'impression que l'opération que vous me décrivez n'est pas celle inscrite dans votre code. En effet vous avez inscrit : Sheets.Add After:=Worksheets("Feuil1"), Count:=nbColonne Où nbColonne est égal à votre nombre de colonne dans la feuille 1. Je pense qu'il y avait une erreur à cet endroit. Cdlt...