Excel VBA Macro pour trier les données. Je n'aime pas la façon dont l'enregistreur de macros crée du code supplémentaire pour le tri. Le tri dans Excel VBA doit être simple. Une ligne de code, avec la colonne à trier, dans quel sens (croissant ou décroissant) et y a-t-il un en-tête.
Regarder la vidéo
- L'enregistreur de macros Excel ne fait pas un bon travail avec le tri des enregistrements.
- À condition que vos données puissent être sélectionnées en utilisant Ctrl + * (connu sous le nom de région actuelle)
- À condition que vous ne triiez pas par couleur ou par icône ou sur plus de trois niveaux
- Utilisez l'ancienne méthode Range (). CurrentRegion.Sort dans Excel
Transcription vidéo
Apprenez Excel à partir du podcast MrExcel, épisode 2093: Trier avec VBA
Hé, bienvenue à nouveau sur le netcast, je suis Bill Jelen. La question d'aujourd'hui de James à Huntsville. James, j'ai utilisé l'enregistreur de macros pour enregistrer l'action de tri des données. Disons donc que James avait besoin de trier ces données par secteur et par clients. Vous arrivez donc ici dans l'onglet Affichage, Macros, Enregistrer une nouvelle macro, SortMyReports, Touche de raccourci Ctrl - Je vais taper Shift + S là-bas, puis cliquez sur OK. D'accord, alors à partir de là, nous faisons: Données, tri, et nous voulons dire que nous voulons trier par secteur, puis ajouter un niveau et trier par client, puis cliquez sur OK. Dans le coin inférieur gauche, nous cliquons sur Arrêter l'enregistrement. Très bien, alors voilà. Semble avoir fonctionné, non?
Mais voici le problème: demain, vous allez avoir plus de données ou moins de données ou, vous savez, peu importe. Et cette macro enregistrée est tout simplement horrible. Jetons un coup d'oeil, je vais faire Alt + F8 et jeter un oeil à SortMyReport, éditerai cela. D'accord, et c'est tout ce qu'ils ont enregistré donc SortFields.Clear, puis ils ont configuré un nouveau tri avec SortFields.Add et ils sont codés en dur, et il y a 568 lignes, et tout le reste.
Maintenant en train de trier dans la journée, j'étais vraiment, vraiment facile. D'accord, puis dans Excel 2007, ils ont ajouté Trier par icône, Trier par couleur, Trier par couleur de police, la possibilité d'avoir 15 niveaux de tri et tout est devenu vraiment, vraiment fou. Donc, je n'utilise plus la macro enregistrée. Je vais juste à la vieille école.
Maintenant, je vais revenir à Excel. Voici les règles pour que ce tri à l'ancienne fonctionne, d'accord. En-tête au-dessus de chaque colonne: cet en-tête doit être sur une ligne et non sur deux lignes. Si vous avez des titres là-haut et c'est bien d'avoir des titres là-haut. Vous avez besoin d'une ligne complètement vide entre vos titres et le premier titre. Si vous avez des notes sur le côté droit: votre femme appelle avec une liste d'épicerie: «Hé, chérie, arrête sur le chemin du retour. Obtenez du lait, des œufs et de la vodka. » Il doit y avoir une colonne complètement vide entre vos données et cela. Et s'il y a des notes standard en bas, assurez-vous qu'il y a une ligne complètement vide entre le dernier bit de données et ces notes.
Mon objectif entier est que nous devrions pouvoir arriver à n'importe quelle cellule: la cellule du coin supérieur gauche ces données et appuyez sur Ctrl + * et il sélectionnera les données à trier. Maintenant, je vais appuyer sur Ctrl +. cela nous amène à ce coin puis Ctrl +. nous amènera au coin inférieur droit, Ctrl +. nous emmène dans le coin inférieur gauche. D'accord, donc si Ctrl + * sélectionne correctement vos données, tout va bien. Si vous mettez votre liste d'épicerie dans la colonne H et que nous voyons que nous montons ici et Ctrl + *, eh bien maintenant, nous trions la liste d'épicerie dans le cadre de la chose et votre liste d'épicerie sera foirée. Ou nous annulerons: si cette ligne n'est pas ici, maintenant nous faisons Ctrl + *, voyons que nous sommes - maintenant ils seront ignorants parce qu'ils n'ont plus de titres, d'accord?
Donc, si vous allez utiliser mon code, assurez-vous que toutes ces règles sont vraies: pas de tri de ma couleur, pas de tri de mon icône, 3 niveaux de tri ou moins. Annuler, d'accord. Alors, voici ce que nous savons: nous savons que chaque jour nos données vont commencer en A5. Si nous ne savons pas combien de lignes ou combien, bien même combien de colonnes nous pourrions avoir. Je ne peux pas imaginer une situation où les colonnes sont interchangées, mais le nombre de lignes va certainement changer. Donc, Alt + F11, nous allons simplement commencer à partir de cette cellule du coin supérieur gauche. Donc Range, dans mon cas est "A5" .CurrentRegion. La région actuelle est cette propriété de bâtiment impressionnante qui dit que nous allons appuyer sur Ctrl + Maj + * et tout ce qui est inclus est ce qui va être trié. Et nous faisons .Sort. .Sort, d'accord.
Maintenant, voici la chose. Si vous voulez faire un tri à un niveau, c'est facile: Key1: =. : = et on dit juste que ça va être Range - Oh j'oublie ce que c'est. C'était Sector, où est Sector? Le secteur est dans la colonne C. Donc C5 dans mon cas, Range («C5») et ensuite, Order1: = xlAscending. J'ai appuyé sur la touche Flèche bas là-bas, puis sur Tab. Très bien maintenant, je pourrais continuer à aller à droite mais je ne vais pas faire ça. Je vais aller à une nouvelle ligne donc espace, soulignement pour aller à une nouvelle ligne, continue cette ligne de code, d'accord? Et si j'ai un tri de deuxième niveau: Key2: = et dans ce cas, je veux trier par client qui est dans la colonne D, donc D5. Et puis, Order2: xlAscending. Beau.
Je n'ai pas de tri de troisième niveau, mais si vous l'avez fait, ce serait Key3 puis Order3. Et puis celui-ci, celui que vous devez faire est Header, d'accord? Donc, Header: = xlGuess c'est là que vous avez beaucoup de problèmes. Et donc nous allons dire xlYes là-bas, certainement comme un en-tête. Même dans l'ancien temps, l'enregistreur de macros utilisait xlGuess. Je déteste qu'Excel devine.
C'est tout. Une ligne de code, c'est tout ce que vous avez à faire et cela fonctionnera avec plus de lignes, moins de lignes. C'est une très belle chose. Très bien, nous allons donc revenir ici à Excel. Ctrl + Maj + S est toujours la chose qui est assignée. Si vous venez de basculer vers VBA et que vous le tapez vous-même, vous pouvez aller sur Alt + F8, trouver le nom de votre macro, cliquer sur Options et taper Ctrl + Maj + S là-dedans ou nous pouvons même l'assigner à un Touche de raccourci ici dans la barre d'outils d'accès rapide. Faites un clic droit, Personnalisez la barre d'outils d'accès rapide où je choisis parmi nos macros. J'ai une macro appelée SortMyReport, je clique sur Ajouter - je déteste le petit organigramme là-bas. Nous allons modifier cela et j'aimerais qu'il y ait une sorte de situation de A à Z, mais bien sûr, il n'y en a pas. Peut-être que cette flèche qui sait, qui sait, choisit n'importe quoi.Le 8-ball magique, je ne sais pas. Je vais choisir ce petit gars ici, cliquez sur OK, cliquez sur OK. Très bien, maintenant nos données sont triées par date, je choisis - et peu importe ce que je choisis. Il va toujours revenir en arrière et le trier à partir de A5, je clique sur le petit gars et mes données sont maintenant triées par secteur, dans le secteur, par client. Cela fonctionne très bien, d'accord?
Donc, si vous êtes un fan de l'enregistreur de macros, eh bien mes meilleurs vœux à vous. Mais le code de l'enregistreur de macros de nos jours pour le tri-tri dans VBA est tellement plus simple; pour revenir en arrière, utilisez simplement essentiellement celui-ci, une ligne de code.
Eh bien, c'est généralement l'endroit où j'essaie de vous faire acheter ce livre, mais aujourd'hui, je pense que vous devriez jeter un coup d'œil à ce livre: Excel 2016 VBA et Macros par Tracy et moi-même. Hou la la! Regarde ça. Je ne savais pas qu'il y avait une version dans une autre langue. Nous vous ferons progresser complètement la courbe d'apprentissage des macros, de l'enregistrement de votre première macro au code dont vous avez besoin.
Eh bien, résumé simple pour aujourd'hui: l'enregistreur de macros Excel ne fait pas un bon travail avec l'enregistrement, le tri: à condition que vos données puissent être sélectionnées en utilisant Ctrl + * qui est connu comme la région actuelle, à condition que vous ne triiez pas comme la couleur ou l'icône ou plus de trois niveaux, utilisez simplement la méthode ancienne Range (). CurrentRegion.Sort dans VBA pour trier.
Je tiens à remercier James d'avoir envoyé cette question. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2093.xlsm
