Bonjour,
Je cherche un moyen d’optimiser ma macro. Elle tourne parfaitement mais peut entraîner quelques ralentissements, et est bien trop longue sur un fichier avec plusieurs milliers de lignes.
Le but de la macro est de filtrer les lignes d’une feuille en fonction de la colonne C.
NORM et YTPN vont dans la feuille Mono mais restent également sur la feuille MAJ, alors que les LUMF sont transférés vers la feuille BOM mais sont également supprimés de la feuille MAJ.
Je n’utilie pas Copy Paste mais Copy Destination pour Bypass le clipboard et réduire le temps de calcul, mais la macro reste assez lente.
Je peux déjà optimiser la macro à l’aide de With / End With, mais je voulais savoir si il y avait une autre manière de faire qui consomme moins de ressources et est plus rapide que ma macro.
Merci à vous
J.
MonoOrBom.xlsm (21,2 Ko)
Bonjour,
Voilà un test en mode TABLO avec un exemple sur + de 5000 lignes
Option Explicit
Option Base 1
Sub MonoProd()
'Déclaration variable
Dim Tablo()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim lr%, lr2%, lr3%, compteur%, colonne%, ligne%
'Attribution variable
Set Ws1 = Sheets("MONO")
Set Ws2 = Sheets("BOM")
lr = Sheets("MAJ").Range("A" & Rows.Count).End(xlUp).Row
lr2 = Sheets("MONO").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("BOM").Cells(Rows.Count, "A").End(xlUp).Row
'Efface les données des 2 feuilles receveuses
Ws1.Range("A2:C" & lr2 + 1).ClearContents
Ws2.Range("A2:C" & lr3 + 1).ClearContents
'FEUILLE MONO
compteur = 0
For ligne = 1 To lr 'Bouble pour récup. valeur NORM et YTPN
If Cells(ligne, 3).Value = "NORM" Or Cells(ligne, 3).Value = "YTPN" Then
compteur = compteur + 1 'Additionne dans le compteur si valeur trouvée
ReDim Preserve Tablo(3, compteur) 'Redim le TABLO
For colonne = 1 To 3
Tablo(colonne, compteur) = Cells(ligne, colonne) 'Place les données dans le TABLO
Next colonne
End If
Next ligne
With Ws1 'recopie en feuille MONO les données du TABLO
.Range(.Cells(2, "A"), .Cells(compteur + 1, 3)) = Application.Transpose(Tablo)
End With
'FEUILLE BOM (même commentaire que ci-dessus
compteur = 0
For ligne = 1 To lr
If Cells(ligne, 3).Value = "LUMF" Then
compteur = compteur + 1
ReDim Preserve Tablo(3, compteur)
For colonne = 1 To 3
Tablo(colonne, compteur) = Cells(ligne, colonne)
Next colonne
'*************************************************************
'* Si suppression des LUMF dans feuille MAJ, ôter l'apostrophe de la cde ci-dessous - rallonge le code
'Cells(ligne, 1).Resize(1, 3).Delete (xlUp)
'*************************************************************
End If
Next ligne
With Ws2 'recopie en feuille BOM
.Range(.Cells(2, "A"), .Cells(compteur + 1, 3)) = Application.Transpose(Tablo)
End With
Set Ws1 = Nothing 'vide la mémoire
Set Ws2 = Nothing
End Sub
MonoOrBom.xlsm (117,7 Ko)
2 « J'aime »
Super @Mimimathy
Merci pour le code que j’ai déjà eu le temps de parcourir un peu, et qui m’a permis de comprendre quelques petites choses que je n’avais pas assimilé.
Cependant, j’ai quelques questions pour être certain de comprendre.
1- J’ai plusieurs autres macros dans mon fichier et plusieurs feuilles. Au vu du code, dois-je activer la feuille avant de lancer la macro ? Ou l’exécution du code est plus rapide avec des blocs With WS(“MAJ”) qui entourent les boucles ?
2- Dans toutes mes macros (qui ne sont pas forcement dans le même module pour faciliter la lecture) j’utilise souvent les mêmes variables/termes (Worksheets(“Something”), ou encore .Cells(Rows.Count, “A”).End(xlUp).Row, etc…). Si je souhaite en faire des variables (qui si j’ai bien compris accélère la lecture du code par excel), quelle est la méthode la plus adaptée ? Est-il utile (ou même intéressant) de call une macro qui se lancera au tout début qui me permet de SET toutes mes variables ? Ça marche ainsi ?
3- Est-il important de reset mes variables en fin de macro (WS = Nothing) ? Ou est-ce juste une bonne habitude à prendre ?
PS : J’ai testé la macro en écrivant le commentaire, et c’est juste génial !!! Impressionnant comment Excel gère différents codes à des vitesses vraiment différentes.
Bonjour,
-1) Tu rajoutes dans la macro au début “sheets(“MAJ”).activate”, comme cela la macro peut-être lancé e depuis une autre feuille
Dim lr%, lr2%, lr3%, compteur%, colonne%, ligne%
sheets("MAJ").activate
'Attribution variable
Set Ws1 = Sheets("MONO")
Set Ws2 = Sheets("BOM")
Le fait de déclarer les variables permet surtout en mettant OPTION EXPLICIT au -dessus déjà de voir les erreurs de compilation. Puis ensuite, si les variables ne sont pas déclarées, elles sont obligatoirement en “Variant”, qui ralenti la marche à suivre et qui prend un espace mémoire considérable.
Le fait que ma macro est plus rapide que la tienne, vient du faite que tous les résultats ne sont pas inscrit sur la feuille, mais placés en mémoire (TABLO), à la fin de la recherche, il colle tous les résultats. le WITH et END WITH sert simplement à éviter de retaper le nom de la feuille en plaçant un “.”, mais il faut bien le comprendre pour éviter les erreurs de syntaxe
-2) Une variable déclarée dans le Sub (Dim) n’est valable que pour la macro,
si elle est déclarée par DIM au-dessus du Sub, elle est valable pour tous le module
si elle est déclarée par PUBLIC SUB au-dessus du Sub, elle est valable pour tous les modules
-3) le SET …=Nothing, permet de décharger en mémoire les données accumulées . Comme il sont en déclarations de variables OBJET , c’est à conseiller.
Ok super, merci pour le temps que tu passes à bien expliquer les choses. Ça me permet de progresser plus rapidement. Pour l’histoire du TABLO c’est effectivement dans le genre de ce que je voulais faire, mais je ne savais pas comment m’y prendre (faire un genre de Ctrl Select pour toutes les valeurs). M’enfin ta méthode marche du tonnerre.
Concernant les variables, ok pour public sub, c’est bien comme ça que je fais. Mais imaginons je déclare une variable Worksheet (qui restera identique durant toute la macro >> WS1 = Worksheets(“MAJ”). Puis-je tout déclarer (enfin les variables que je connais) avant même le début de toutes mes macros (cf exemple si dessous avec seulement une variable déclarée).
Public Sub Ws1 as Worksheet
Sub SetVar
Set Ws1 = worksheets("MAJ")
Set ...
Set ...
Set ...
x = ...
End Sub
Sub MaMacro
'Call SetVar
'Mon reste du code
Call Mamacro2
End Sub
Sub Mamacro2
End Sub
EDIT : Comment la variable TABLO() est-elle déclarée ? Quel type de variable est-ce (enfin je suppose que c’est un tableau à 2 dimension mais pas certain, et alors pourquoi ne pas l’indiquer entre les parenthèses lors de la déclaration des variables et ne pas déclarer directement le String ?) ? Ne doit-on pas aussi la Set = Nothing au vu du nombre de données stockées dedans ?
Re,
Dans ton exemple la variable TABLO() peut-être déclarée en STRING vu que les données sont du texte
si on ne la déclare pas, c’est que dans certains montage, il peut y avoir une colonne texte, une autre date, une autre chiffre,
donc on ne la déclare pas, comme cela elle est variante.
Si vraiment tu veut le vider de sa mémoire, dans ce cas utilise "Erase Tablo"
Mais comme c’est un tableau dynamique, il se vide à chaque fois que tu lances la macro par la ligne
REDIM PRESERVE qui redimensionne le tablo
1 « J'aime »
Hello,
Je me permets de remonter vite fait ce sujet puisque j’ai encore une question à ce sujet. Dois-je isoler la macro dans un module (seul, sans autre macro) à cause de l’option base 1 ?
Merci par avance pour la réponse,
Bonjour,
Cela n’a rien à voir,
sauf si tu refais une macro en utilisant le principe d’un TABLO où il te faudra en tenir compte pour la gestion des N° de colonnes
1 « J'aime »
Et cette Option est uniquement déclarée dans la partie déclaration ou on peut la changer au cours de la macro ? avec Set Base = 0 par exemple
La valeur par défaut est 0, l’instruction Option Base n’est jamais obligatoire. Si utilisée, l’instruction doit apparaître dans un module avant toutes les procédures. OptionBase peut apparaître qu’une seule fois dans un module et doit précéder les déclarations du tableau qui déclare ses dimensions.
Il ne faut oublier la touche de fonction du clavier F1. En se positionnant sur une fonction, l’aide d’Office te donne les instructions.