Organiser un tableau en changeant la colonne de tri

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Trier automatiquement et colonne clé

Avec cette nouvelle astuce VBA Excel, nous allons voir comment trier automatiquement un tableau de longueur variable, tout en définissant à la volée quelle est la colonne devant être utilisée en priorité pour la réorganisation alphabétique des données.

Classeur Excel à télécharger
Pour la mise en place de cette solution, nous suggérons d'appuyer l'étude sur un classeur hébergeant de riches données à réorganiser.

• Télécharger le classeur trier-tableaux.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur les boutons de sécurité Activer la modification et Activer le contenu,

Nous retrouvons bien la base de données que nous évoquions. Elle s'étend de la colonne B à la colonne E sur plusieurs centaines de lignes en commençant par la ligne 3. De même, vous notez la présence d'un bouton (Trier) en haut à gauche de la feuille. Il est déjà associé à une procédure VBA, vierge pour l'instant bien sûr.

Les variables de coordonnées
Pour débuter la construction du code VBA attaché, nous devons commencer par déclarer les variables qui permettront de stocker les coordonnées des cellules clés.

Remarque importante : Nous allons coder cette macro dans la feuille de ce classeur. Mais il est important de comprendre qu'elle pourrait très bien ou devrait être ajoutée au personal.xlsb (Le modèle Excel), pour trier n'importe quel tableau de n'importe quel classeur, depuis un bouton de ruban personnalisé.

• Après l'instruction de gestion d'erreur, ajouter les déclarations de variables suivantes :

Sub trier()
On Error Resume Next
Dim celDep As String: Dim celTri As String
Dim ligneFin As Integer: Dim colonneFin As Integer

End Sub

Nous déclarons les variables celDep et celTri comme des textes (String). Par le biais de boîtes de dialogue (InputBox), c'est en effet l'utilisateur qui va saisir les coordonnées de la première cellule du tableau et les coordonnées de la cellule de titre, à utiliser comme clé pour le tri alphabétique. Ensuite, nous déclarons les variables ligneFin et colonneFin comme des entiers. Nous allons en effet les utiliser pour connaître la ligne et la colonne de la dernière cellule du tableau, grâce à une méthode précieuse de l'objet Cells. En connaissant les bornes de début et de fin du tableau, nous pourrons facilement organiser le tri souhaité.

Coordonnées des cellules pour le tri
Maintenant, nous devons communiquer avec l'utilisateur par le biais d'une boîte de dialogue spéciale. Un InputBox lui permet en effet de répondre grâce à une zone de saisie. Dans ces zones de saisie, nous devons prélever les coordonnées de la première cellule du tableau et celles de la cellulede titre à utiliser comme colonne de tri.

• A la suite du code, ajouter les instructions VBA suivantes :

...
celDep = InputBox("Coordonnées de la première cellule du tableau", , "B3")
MsgBox "Première cellule du tableau : " & celDep

celTri = InputBox("Coordonnées de la cellule de titre pour le tri", , "D3")
MsgBox "Cellule d'entête pour le tri : " & celTri
...

Nous utilisons deux MsgBox pour afficher les coordonnées saisies et prouver que nous sommes en mesure de stocker ces références en variables. Vous remarquez de même que nous exploitons le troisième argument de la fonction InputBox. Pour simplifier les actions de l'utilisateur, nous suggérons des coordonnées par défaut.

• Enregistrer les modifications (CTRL + S) et exécuter le code VBA (F5),
• Valider la première boîte de dialogue qui propose la cellule B3 en point de départ,
• Puis valider celle qui suit, restituant ces coordonnées,
• A la nouvelle invite, saisir les coordonnées d'une cellule de titre, par exemple : C3,
• Puis, valider par Ok,

Comme vous pouvez le voir, les coordonnées tapées sont parfaitement restituées à l'écran, donc stockées dans les variables respectives.

Dernière ligne et dernière colonne
Pour trier un tableau, nous avons besoin de connaître ses bornes et pas seulement sa cellule de départ. Il est donc maintenant question de déceler la ligne et la colonne de la dernière cellule de ce dernier. Et pour cela, nous allons exploiter une méthode particulière de l'objet Cells qui représente la collection des cellules de la feuille. Cette méthode se nomme SpecialCells.

• Valider le dernier MsgBox pour revenir dans l'éditeur VBA Excel,
• Préfixer les deux MsgBox d'une apostrophe (') pour passer les deux lignes en commentaires,
• Puis, à la suite du code, ajouter les instructions VBA suivantes :

...
ligneFin = Cells.SpecialCells(xlCellTypeLastCell).Row
colonneFin = Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox ligneFin & "-" & colonneFin
...

Quand vous ouvrez la parenthèse de la méthode SpecialCells, VBA propose une liste des valeurs possibles en paramètre. C'est la valeur xlCellTypeLastCell qui désigne la dernière cellule. De fait, avec les propriétés respectives Row et Column, nous stockons la ligne et la colonne de la dernière cellule du tableau dans les variables ligneFin et colonneFin. Enfin et comme précédemment, nous exploitons une fonction MsgBox temporaire pour restituer ces informations à l'écran.

• Enregistrer les modifications (CTRL + S) et exécuter le code (F5),
• Valider la première invite pour accepter la cellule de départ,
• Puis, valider la seconde pour accepter la cellule de tri,

Dès lors, la boîte de dialogue du MsgBox s'affiche et restitue les coordonnées de la dernière cellule du tableau. La colonne 5 représente la colonne E qui est bien la dernière colonne du tableau. En revanche, la ligne 1000 est la dernière à proposer une mise en forme avec des bordures mais n'offre pas de contenu. Ce n'est pas un souci dans la mesure où le tri ne va pas considérer ces cellules vides. Sachez que si des données venaient à être ajoutées dynamiquement à ce tableau, au-delà de la ligne 1000, cette méthode SpecialCells avec sa propriété Row, renverrait bien le nouvel indice de ligne ainsi repoussé.

La colonne de tri
Maintenant que les bornes sont connues, avant d'organiser le tri du tableau, des réglages sont nécessaires. Il est notamment question de définir la colonne à utiliser comme clé de réorganisation. Sur cette colonne désignée par l'utilisateur, les informations seront triées alphabétiquement et les données attachées suivront.

• Valider la dernière boîte de dialogue pour revenir dans l'éditeur VBA Excel,
• Passer le dernier MsgBox en commentaire en le préfixant d'une apostrophe,
• Puis, ajouter les deux lignes VBA suivantes à la suite du code :

...
With Worksheets("bdd").Sort.SortFields
.Clear
.Add2 Key:=Range(celTri & ":" & Left(celTri, 1) & ligneFin)
End With
...

Grâce à la collection Worksheets, nous pointons sur la feuille nommée bdd. Avec sa méthode Sort, nous engageons des réglages sur le tri à suivre. Avec la méthode Clear de sa propriété SortFields, nous effaçons les anciens réglages pour ne pas les mélanger avec les nouveaux. Puis, grâce à la méthode Add de cette même propriété, nous définissons la colonne à utiliser pour le tri (Key:=). Avec l'objet Range, nous définissons la première cellule de cette colonne (celTri) que nous associons (:) avec la dernière. Pour cela, nous prélevons l'indice de colonne par la gauche (Left(celTri, 1)) pour lui greffer l'indice de la dernière ligne (ligneFin). C'est ainsi que nous définissons la colonne complète pour le tri. Vous l'avez remarqué, le tout est regroupé dans un judicieux bloc With pour ne pas répéter inutilement, les objets, méthodes et propriétés communes.

Trier le tableau aux bornes variables
Pour finir, nous allons de nouveau exploiter la méthode Sort dans un bloc With afin d'organiser le tri sur l'intégralité des cellules du tableau dont nous connaissons les bornes, tout en excluant la ligne de titre qui doit rester en place.

• A la suite du code, ajouter les instructions VBA suivantes :

...
With Worksheets("bdd").Sort
.SetRange Range(celDep & ":" & Cells(ligneFin,colonneFin).Address)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
...

Grâce à la méthode SetRange et l'objet Range, nous désignons le tableau complet en partant de la première cellule (celDep) pour rejoindre (:) la dernière (Cells(ligneFin, colonneFin).Address), grâce aux variables ligneFin et colonneFin exploitées dans l'objet Cells. C'est la propriété Address qui réalise la transformation en coordonnées de cellules. Avec la propriété Header, nous indiquons de ne pas considérer la ligne de titre. Avec la propriété Orientation, nous organisons un tri croissant sur la colonne clé désignée en amont. Enfin, avec la méthode Apply, nous lançons l'opération de réorganisation selon tous ces réglages prédéfinis.

• Enregistrer les modifications et exécuter le code,
• Valider la première invite pour conserver la cellule de départ définie par défaut,
• A la seconde, saisir les coordonnées de la cellule de titre, pour le tri,
• Puis, valider par le bouton Ok,

Comme vous pouvez le voir, tout le tableau est automatiquement réorganisé alphabétiquement sur la colonne clé définie par l'utilisateur. À tout moment, cette colonne de tri peut être modifiée en réexécutant le programme. Et bien entendu, toutes les données qui seraient ajoutées à la suite du tableau seraient naturellement considérées dans le tri par le code VBA, puisqu'il recherche les bornes de ce dernier avant de lancer le traitement.

Le code VBA complet du programme est le suivant :

Sub trier()
On Error Resume Next
Dim celDep As String: Dim celTri As String
Dim ligneFin As Integer: Dim colonneFin As Integer

celDep = InputBox("Coordonnées de la première cellule du tableau", ,"B3")
'MsgBox "Première cellule du tableau : " & celDep

celTri = InputBox("Coordonnées de la cellule de titre pour le tri", ,"D3")
'MsgBox "Cellule d'entête pour le tri : " & celTri

ligneFin = Cells.SpecialCells(xlCellTypeLastCell).Row
colonneFin = Cells.SpecialCells(xlCellTypeLastCell).Column
'MsgBox ligneFin & "-" & colonneFin

With Worksheets("bdd").Sort.SortFields
.Clear
.Add2 Key:=Range(celTri & ":" & Left(celTri, 1) & ligneFin)
End With

With Worksheets("bdd").Sort
.SetRange Range(celDep & ":" & Cells(ligneFin, colonneFin).Address)
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With

End Sub