Faire une tableau multi collonnes et sources

Question

Avec un souci de mise a jour de multiple liste je suis en train de faire un petit classeur usine a gaz !!! Mon souci : dans le classeur joint ci dessous il y a une feuille "Pointage Mercredi" qui malgré mon insistance est rarement a jour ! Le but: Plutôt que ce soit fait manuellement (jamais !!) j'aimerais que la liste des élèves par classe évolue suivant la valeur vrai faux de plusieurs colonnes d'un tableau. Si en colonne "BE" de la feuille "liste des enfants" est "VRAI" le nom est dans la liste de la feuille "Pointage Mercredi" ainsi que Si en colonne "BC" de la feuille "liste des enfants" est "VRAI" le nom est dans la liste de la feuille "Pointage Mercredi" Ceci étant d'avoir le mème type de feuille sur une seule page pour évité des tonnes de papiers et des mises a jour non faites .. je vous remercie si vous avez des pistes car j'ai tester avec TCD ou avec les occurrences créer pour la feuille "liste élèves par classe" mais si modif ça passe par dessus .. Merci Nadia...

MFerrand · Answer

Je n'apprécie guère les modifications d'interfaces imposées, surtout quand j'ignore si le réalisateur maîtrise parfaitement ce sujet... et je ne tiens pas à me retrouver par la suite avec des modifications qui ne se seraient pas éliminées à la fermeture du fichier... D'autre part, ce type de modifications imposées est toujours gênant lorsque l'on doit intervenir... Je souhaite donc toujours être prévenu de leur présence et comme beaucoup d'intervenants qu'elles soient désactivées pour travail sur le Forum. Dans ton cas, pas grand chose comme modifs, mais... Ta disposition ne facilite pas vraiment les choses, mais un procédure de mise à jour de ta feuille de pointage est tout à fait faisable. Une question d'abord, j'ai cru déceler une correspondance entre cellule de nom grisée et VRAI en BC. Mais 5 noms avec VRAI/BC dont 4 seulement sont grisés ? Qu'en est-il ? Car il conviendrait de pouvoir effacer la mise en forme pour la rétablir ensuite lors d'une mise à jour (qui ne touchera que les cellules noms/prénoms de la feuille). Par ailleurs, il conviendrait que les libellés de classes soient rigoureusement identiques à ceux de la liste enfants (je l'ai déjà fait en prévision). Je suggèrerais également que ta légende en bas de tableau, qui s'étale en B, C, G et I, J soit rassemblée sous la 1re liste (A:H). Elle évoluera avec les variations de celle-ci (insertions ou suppressions de lignes), mais restera groupée, alors que la partie figurant sous la 2e liste(I:P) a toutes chances de se désaligner de l'autre au fil des mises à jour. Cordialement...

MFerrand · Answer

Une vraie gymnastique... ! Il me reste à tester. Indispensable, vu les risques d'erreurs dans les décomptes de lignes ou colonnes. Je ferai ça dans la journée, mais je te signale dès maintenant que : Le lancement la procédure de mise à jour, présuppose que la liste enfants soit triée par ordre alpha. J'ai noté la présence d'une procédure opérant un tri alpha sur les noms : j'ai pensé y faire appel, mais Bouton4_Clic n'est pas un nom particulièrement convivial ! J'ai pensé le modifier mais n'ai pas trouvé de bouton d'attache, je ne sais donc pas s'il y en a un... J'ai donc laissé mais noter que le tri de la liste enfants (par noms, et pour la régularité de la chose prénoms) devrait être introduit en début de procédure... J'ai noté aussi que les BTS sont exclus du pointage, et ont en effet FAUX dans les deux colonnes tests, ce qui m'a paru logique. Je n'en ai donc pas tenu compte puisqu'ils ne seront pas retenus. Mais même s'il y avait une mention VRAI, il n'apparaîtront pas dans le pointage, dans la mesure où les classes BTS n'y figurent pas... S'il devait en être autrement, le signaler. La proc. de mise à jour procèdera en détruisant chaque liste de pointage (groupe de 8 colonnes) pour la remplacer. Ce qui éliminera les indications éventuelles des colonnes 3 à 8 de chaque liste. Si cela ne colle pas avec l'utilisation de la feuille de pointage, le signaler car là, il faudra changer de méthode si des données devaient être conservées. Cordialement...

ceuzin · Answer

Ne pas ouvrir le fichier. Il supprime tous les menus Excel. Ceuzin...

MFerrand · Answer

Il te faudra être un peu patiente... Je n'ai pu passer au test de mon travail de dimanche que ce matin et j'ai essuyé une salve d'erreurs 9 avant de m'apercevoir que j'avais mémorisé l'emplacement des lignes de titres et le nombre de lignes pour chaque classe, mais en même temps j'avais calculé l'écart en nombres de lignes avec la même liste mise à jour, et pour des raisons pratiques j'avais procédé à l'actualisation du nombre de lignes avant de m'occuper de la suite... Je n'ai pas eu le courage de me lancer dans la rectification, d'autant que je prépare mon départ et suis déjà en même temps dans mes bagages..., et que pour bien faire, il vaut mieux que je reprenne une vérification d'ensemble de la manipulation... Si je peux faire avant mon départ mercredi, tu l'auras, mais demain je suis pas mal occupé et ne peux rien promettre... Cordialement et bonne fin de soirée...

MFerrand · Answer

J'ai au moins bien fait d'attendre d'examiner le problème au calme ! L'erreur n'était pas où j'avais cru la déceler, il y en avait une ailleurs tout de même mais qui n'aurait pas déclenché d'erreur d'exécution... Bref ! ça fonctionne apparemment ! A toi de voir si cela convient, et vérifier qu'il n'y a pas d'erreur... J'ai fait une vérification détaillé en nombre sur la première liste : nombres par classe avant et après, position des noms de classe et des noms à griser, cela collait, donc pas de raison que cela ne colle pas aussi sur les 3 autres listes de la feuille. Je n'ai pas fait de vérification sur les noms mais dans la mesure où on les récupère dans l'ordre, en étant bon en nombre, cela devrait l'être aussi (mais mieux vaut toujours vérifier...) Voilà la proc. finale (qui sera éventuellement à compléter sur les points déjà discutés...). Elle se trouve en Module2, et je l'ai affectée à un bouton intitulé Mise à jour (à toi de voir si tu modifies ce dispositif...) Sub MajPointage() Dim d As Object, k, gr, n%, i% Dim Tcl(), Tnp(), cl%, kk%, itm Set d = CreateObject("Scripting.Dictionary") With &#91;Tableaulistesenfants&#93; For i = 1 To .Rows.Count If .Cells(i, 55) Or .Cells(i, 57) Then k = .Cells(i, 5): gr = .Cells(i, 55) n = CInt(d(k)) + 1 d(k) = n: k = k &amp; "-" &amp; n d(k) = gr &amp; "|" &amp; .Cells(i, 1) &amp; "|" &amp; .Cells(i, 2) End If Next i End With Application.ScreenUpdating = False With Worksheets("Pointage Mercredi") For kk = 1 To 25 Step 8 cl = 0: i = 1: n = 0 ReDim Tcl(3, 0): Tcl(1, 0) = 0 Do If .Cells(i, kk).MergeCells Then cl = cl + 1: ReDim Preserve Tcl(3, cl) Tcl(0, cl) = .Cells(i, kk) Tcl(1, cl) = i Tcl(2, cl - 1) = i - Tcl(1, cl - 1) - 1 n = n + Tcl(3, cl - 1) - Tcl(2, cl - 1) Tcl(3, cl) = CInt(d(Tcl(0, cl))) End If i = i + 1 Loop While .Cells(i, kk) &lt;&gt; "" Tcl(2, cl) = i - Tcl(1, cl) - 1 n = n + Tcl(3, cl) - Tcl(2, cl) If n &gt; 0 Then .Cells(2, kk).Resize(n, 8).Insert xlShiftDown ElseIf n &lt; 0 Then .Cells(2, kk).Resize(-n, 8).Delete xlShiftUp End If ReDim Tnp(1 To i + n - 1, 1): n = 0: gr = "" For cl = 1 To UBound(Tcl, 2) k = Tcl(0, cl) &amp; "-" n = n + 1: Tnp(n, 0) = Tcl(0, cl) Tcl(1, cl) = n For i = 1 To Tcl(3, cl) itm = Split(d(k &amp; i), "|") n = n + 1: Tnp(n, 0) = itm(1): Tnp(n, 1) = itm(2) If itm(0) Then gr = gr &amp; ";" &amp; n Next i Next cl With .Range(.Cells(1, kk), .Cells(n, kk + 7)) .Clear .Resize(, 2).Value = Tnp With .Font .Name = "Arial": .Size = 7 End With .VerticalAlignment = xlCenter .RowHeight = 9.25 .Borders.Weight = xlThin Application.DisplayAlerts = False For cl = 1 To UBound(Tcl, 2) With .Rows(Tcl(1, cl)) .MergeCells = True With .Font .Name = "Calibri": .Bold = True End With .HorizontalAlignment = xlCenter .Interior.Color = vbYellow .Borders(xlEdgeTop).Weight = xlMedium .Borders(xlEdgeBottom).Weight = xlMedium End With Next cl Application.DisplayAlerts = True If gr &lt;&gt; "" Then gr = Split(gr, ";") For i = 1 To UBound(gr) .Cells(CInt(gr(i)), 1).Resize(, 2).Interior.Color = RGB(217, 217, 217) Next i End If End With If kk = 1 Then .Range("B" &amp; n + 11) = Date Next kk End With End Sub Je crois que des explications ne sont pas inutiles (car la suivre au niveau calculs est déjà un peu casse-tête). Et tu as intérêt à savoir ce qu'elle fait exactement et successivement. Des explications vont donc suivre morceau par morceau... En attendant, le fichier te permettra de tester. Cordialement...

MFerrand · Answer

La procédure comprend 2 parties :

Première partie : prélèvement des données (soit les noms par classe des élèves à inscrire sur la feuille de pointage) sur la liste des élèves.

Seconde partie : Réorganisation des données selon la structure de la feuille de pointage, et remplacement des données antérieures par les nouvelles, en ajustant la feuille au niveau de chacune des 4 listes en nombre de lignes.

La première partie est assez simple (et sera facile à suivre) :

Sub MajPointage()
    Dim d As Object, k, gr, n%, i%
    Dim Tcl(), Tnp(), cl%, kk%, itm
    Set d = CreateObject("Scripting.Dictionary")

On débute par les déclarations de variables, comme il se doit. J'ai séparé les déclarations en 2 lignes, la première déclare les variables utilisées dans la première partie (et qui le seront aussi dans la seconde), la deuxième les variables supplémentaires néessaires au traitement des données à la suite de leur recueil.

On va utiliser l'outil dictionaire pour recueillir les données et on initialise donc la variable d à cette fin.

A ce stade, immédiatement avant l'initialisation du dico, compte tenu de l'utilisation que tu fais du pointage, il serait peut-être judicieux d'introduire un message d'avertissement (que les données antérieures seront effacées) laissant la possibilité à l'utilisateur de poursuivre ou non...

De même, avant de passer au recueil de données, il conviendra le cas échéant d'opérer un tri par Nom (et Prénom) de la liste élèves. Si tu disposes d'une procédure qui le fait, tu peux l'introduire... Sinon je peux ajouter ces petites modifs si nécessaire.

    With [Tableaulistesenfants]
        For i = 1 To .Rows.Count
            If .Cells(i, 55) Or .Cells(i, 57) Then
                k = .Cells(i, 5): gr = .Cells(i, 55)
                n = CInt(d(k)) + 1
                d(k) = n: k = k & "-" & n
                d(k) = gr & "|" & .Cells(i, 1) & "|" & .Cells(i, 2)
            End If
        Next i
    End With

Et voilà le code qui boucle la 1re partie (recueil des données). Commentaires :

- La liste élèves est constituée par un tableau Excel, on va donc l'utiliser directement : pour ces tableaux Excel crée automatiquement un nom lequel recouvre la partie données du tableau (hors ligne d'en-tête). L'avantage de son utilisation est qu'on le parcourt de la ligne 1 à la dernière (égale au nombre de lignes qu'il contient) sans avoir à se préoccuper de la correspondance avec les numéros de ligne de la feuille (que l'on peut donc parfaitement ignorer).

On met donc déjà le tableau sous bloc With...

- On initie une boucle For... Next (variable i) sur les lignes du tableau...

- A chaque ligne on vérifie la condition de recueil pour pointage : elle est renvoyée par les cellules en col. 55 (BC) et 57 (BE). Ces cellules devant contenir une valeur booléenne, on ne se préoccupe pas de la valeur... si l'une ou l'autre renvoie VRAI la condition est réalisée (une cellule vide renverra FAUX...)

Pour chaque ligne où la condition est réalisée :
- on récupère dans une variable (k) la valeur en col. 5 (E), le nom de la classe ;
- on récupère dans une autre variable (gr) la valeur en col. 55, qui sera VRAI ou FAUX (si VRAI c'est qu'il s'agit d'un nom à griser) ;
- on appelle l'élément de dico d(k) pour ajouter 1 a sa valeur (convertie en nombre, un dico ne contenant que du texte) ;
- et on affecte cette valeur incrémentée à l'élément de dico.

A noter que si l'élément n'existe pas, il sera créé par son invocation, sans contenu, et donc au premier élève d'une classe, on aura créé un élément dico au nom de la classe et recueilli la valeur 1 dans une variable ; aux élèves suivants de la même classe, l'élément existant on récupère sa valeur que l'on incrémente pour la lui réaffecter. A la fin de l'opération, on aura donc un élément de dico dont la clé sera le nom de classe, et le contenu le nombre d'élèves retenus de ladite classe.

On poursuit :

- en modifiant la clé k pour faire suivre le nom de classe d'un tiret et du nombre incrémenté que l'on vient de définir (qui sera le numéro d'ordre de l'élève) ;
- ce qui donne lieu à création d'un nouvel élément de dico avec cette clé, à laquelle on affecte comme contenu 3 éléments séparés par le caractère pipe (|) : la valeur de gr (qui permettra de savoir si le nom est à griser, le nom (col. A), le prénom (col. B).

A la fin de cette opération, on disposera d'un élément dico pour chaque élève à inscrire dans la feuille pointage, et d'un élément dico pour chaque classe (indiquant le nombre d'élèves de la classe à inscrire).

Pas de difficulté je pense sur cette première partie, une fois bien perçu les deux types d'éléments dico constitués.

Je pause pour m'occuper de mon repas (j'ai retrouvé mon décalage horaire habituel).

MFerrand · Answer

Venons-en à la seconde partie. Elle va se dérouler en 4 phases pour chacune des 4 listes de la feuille pointage :

1) analyse de la structure de la liste existante et des éléments formels de son évolution ;

2) ajustement du nombre de lignes de la liste actuelle au nombre de ligne de la liste mise à jour ;

3) constitution de la nouvelle liste (en tableau) ;

4) effacement des données antérieures et affectation de la nouvelle liste, puis mise en forme.

Chaque liste occupe 8 colonnes de la feuille pointage. Elle comporte une liste de noms et prénoms dans les deux premières colonnes, répartis en sous-listes par classe, chaque sous-liste est précédée d'une ligne indiquant la classe, cette ligne est fusionnée.

Ouverture de la seconde partie :

    Application.ScreenUpdating = False
    With Worksheets("Pointage Mercredi")
        For kk = 1 To 25 Step 8

On aura à intervenir sur la feuille, on inhibe donc la mise à jour de l'affichage, de façon à accélérer l'exécution.

Tout se déroule sur la feuille Pointage Mercredi, que l'on met donc utilement sous bloc With.

On initialise une boucle de 1 à 25 d'un pas 8 qui permettra de passer tour à tour sur chacune des premières colonnes de chaque liste de la feuille (1, 9, 17, 25).

Tout le code inclu dans la boucle se déroulera à l'identique pour chacune des listes et se décompose dans les 4 phases pré-citées.

La structure de la liste (1re phase) va faire l'objet d'un tableau Tcl dans lequel on va recueillir les caractéristiques de chaque sous-liste (par classe).

On va recueillir par classe dans ce tableau : le nom de la classe, sa ligne actuelle dans la liste, le nombre de noms dans la classe, le nombre de noms à inscrire lors de la mise à jour. Soit 4 éléments, la 1re dimension (fixe) du tableau sera donc établie à 3 (0 à 3). Les classes en nombre variable donneront lieu à incrémentation de la 2e dimension du tableau, chaque fois que l'on rencontrera une nouvelle classe dans la liste.

On va utiliser la mise en forme pour détecter chaque classe : en parcourant les lignes de la liste, chaque fois que l'on rencontre une cellule fusionnée, l'on est en présence d'un intitulé de classe. L'élément d'indice 0 (nom) de la classe est fourni par la valeur de la cellule, celui d'indice 1 (ligne) par la ligne atteinte dans la boucle sur la liste, l'élément d'indice 3 (nouveau nombre dans la classe) nous est fourni de l'extérieur par la valeur de l'élément dico au nom de la classe. Reste l'élément d'indice 2 (nombre actuel) : il est simplement calculé par

ligne atteinte lors détection classe suivante - ligne classe -1,

on le calcule donc à chaque classe pour la classe précédente, et pour la dernière classe après sortie de la boucle (la valeur atteinte par la variable de boucle correspondant à la ligne qui suit la liste (même calcul donc !)

Parallèlement, on calculera l'écart entre nouveau nombre et ancien nombre en vue de la phase suivante (ajustement longueur liste).

            cl = 0: i = 1: n = 0
            ReDim Tcl(3, 0): Tcl(1, 0) = 0
            Do
                If .Cells(i, kk).MergeCells Then
                    cl = cl + 1: ReDim Preserve Tcl(3, cl)
                    Tcl(0, cl) = .Cells(i, kk)
                    Tcl(1, cl) = i
                    Tcl(2, cl - 1) = i - Tcl(1, cl - 1) - 1
                    n = n + Tcl(3, cl - 1) - Tcl(2, cl - 1)
                    Tcl(3, cl) = CInt(d(Tcl(0, cl)))
                End If
                i = i + 1
            Loop While .Cells(i, kk) <> ""
            Tcl(2, cl) = i - Tcl(1, cl) - 1
            n = n + Tcl(3, cl) - Tcl(2, cl)

Pour chaque liste donc, on commence par réinitialiser les variables utilisées : cl (incrémentation du tableau à chaque classe trouvée) à 0, i (variable de boucle sur les lignes de la liste) à 1 (on part de la ligne 1), et n (cumul des écarts en nombre nouvelle/ancienne liste) à 0.

On redimensionne le tableau Tcl à 3 (1re dim. comme indiqué) et 0 en seconde dim., et on initialise l'élément Tcl(1, 0) à 0 (ce qui permet d'initialiser les calculs pour les classes, lors de l'inscription des éléments de la 1re classe, le calcul se faisant pour la classe précédente donnera 0 inscrit en Tcl(2, 0), ce qui ne représente rien mais ce calcul doit pouvoir s'accomplir pour que les suivants se fassent également...)

On initialise enfin une boucle Do... Loop sur les lignes (i) de la première colonne de la liste (kk). Le code interne à la boucle se termine sur l'incrémentation de la variable ligne et s'interrompt si la cellule est vide (c'est que la liste est terminée).

On n'intervient dans la boucle que sur détection d'une cellule fusionnée :

on incrémente la variable cl et on redimensionne le tableau en préservant son contenu ;
on affecte les éléments nom de classe et ligne de la classe ;
on calcule la valeur de l'élément nombre ancien pour la classe précédente ;
on calcule l'écart nouveau - ancien nombre, cumulé dans n, au niveau classe précédente ;
on affecte l'élément nouveau nombre (extrait du dico).

Une fois sorti de boucle, on complète les éléments manquants pour la dernière classe trouvée (élément d'indice 2 et cumul écarts nombres).

On obtiendrait le tableau suivant en l'affectant (transposé) sur une feuille de calcul, par exemple pour la première liste de la feuille : le tableau est constitué par la partie quadrillée. en jaune la partie utile.

On peut voir aussi que le calcul des écarts cumulés donnera dans cette situation : 4. Ce qui signifie qu'il faudra allonger la liste décrite de 4 lignes pour la mettre à la longueur de la nouvelle liste.

Fin de la première phase.

MFerrand · Answer

Réglons rapidement le sort de la 2e phase :

            If n > 0 Then
                .Cells(2, kk).Resize(n, 8).Insert xlShiftDown
            ElseIf n < 0 Then
                .Cells(2, kk).Resize(-n, 8).Delete xlShiftUp
            End If

Si la valeur de n est positive, la longueur de la liste augmente, et on insère donc autant de lignes.

Si elle est négative, la longueur diminue, et on supprime donc autant de lignes (que la valeur absolue du nombre).

Si la valeur est égale à 0, la longueur ne bouge pas, et on ne fait rien.

Cet ajustement fait, on peut passer à la 3e phase : constitution d'un tableau représentant la nouvelle liste, soit un tableau à 2 colonnes et dont le nombre de lignes est défini par la variable i qui au terme de la 1re phase a comme valeur le numéro de ligne vide suivant l'ancienne liste, -1 donc pour éliminer cette ligne, mais en ajoutant n, valeur de l'écart entre nouvelle liste et ancienne. On dimensionnera les lignes à partir de l'indice 1, de façon à avoir une correspondance avec les lignes de la feuille (utile pour les mises en forme à opérer lors de la 4e phase... le même souci n'intervenant pas pour les colonnes, on restera sur les indices 0 et 1 en ce qui les concerne).

Ce qui est à considérer c'est que l'on dimensionne exactement le tableau résultant avant de commencer à le servir.

Pour le servir on incrémentera une variable avant de servir successivement chaque ligne (le dimensionnement étant calculé sur la longueur de la liste, vue précédemment, dont la répartition figure sur le tableau constitué lors de la 1re phase, tout doit cadrer exectement ! [si l'on n'a pas fait d'erreur en route, bien sûr ! mais ça cadrera après rectification des erreurs éventuelles...)

Une liste étant composée de sous-listes par classes, on va utiliser le tableau Tcl consignant les caractéristiques de chacune, en particulier le nom de la classe (en indice 0) et le nombre de noms dans la classe (indice 3).

Cela sera peut-être plus facile à suivre avec le code correspondant :

            ReDim Tnp(1 To i + n - 1, 1): n = 0: gr = ""
            For cl = 1 To UBound(Tcl, 2)
                k = Tcl(0, cl) & "-"
                n = n + 1: Tnp(n, 0) = Tcl(0, cl)
                Tcl(1, cl) = n
                For i = 1 To Tcl(3, cl)
                    itm = Split(d(k & i), "|")
                    n = n + 1: Tnp(n, 0) = itm(1): Tnp(n, 1) = itm(2)
                    If itm(0) Then gr = gr & ";" & n
                Next i
            Next cl

Redimensionnement du tableau Tnp comme indiqué. Réinitialisation des variables utilisées : n (incrémentation des ligne du tableau à servir) à 0 (ajoute 1 avant chaque utilisation), et gr (qui ici va être utilisée pour lister les lignes des noms à griser sous forme de chaîne texte) en texte vide ("").

On initialise ensuite une boucle sur chaque classe du tableau Tcl. Pour chaque classe, on préinitialise la variable k qui représentera une clé de dico avec le nom de la classe suivi d'un tiret, on incrémente n et on insère sur cette ligne du tableau le nom de la classe. On affecte également la valeur de n à l'élément du tableau Tcl représentant la ligne où se trouve l'intitulé de classe (il se substitue à l'ancienne ligne et l'on pourra s'y référer pour mettre en forme cette ligne ensuite...).

On ouvre ensuite pour la classe une boucle de 1 au nombre de noms devant y figurer (noté à l'indice 3 du tableau Tcl), ce qui permet de compléter la clé par le numéro d'ordre pour appeler tour à tour chaque nom de la classe consigné dans un élément dico.

On splitte le contenu de l'élément (dans la variable itm) sur la base du séparateur "|" : la variable contiendra donc un tableau de 3 éléments (indices 0 à 2), les éléments d'indice 1 et 2 (Nom et Prénom) sont affectés au tableau final après incrémentation, l'élément 0 est testé pour savoir s'il s'agit d'un nom à griser et si c'est le cas la ligne est conservée dans la variable gr (les lignes consignées sont séparées par des points-virgules).

A l'issue de cette phase on dispose d'un tableau constituant la nouvelle liste, des numéros de lignes des intitulés de classes (dans le tableau Tcl)), et des numéros de lignes des noms à griser (éventuellement).

Il ne restera donc plus qu'à affecter et remettre en forme...

MFerrand · Answer

La dernière phase consiste à mettre en place la nouvelle liste et à la remettre en forme en suivant (avec quelques différences que j'indiquerai) pour l'essentiel la mise en forme initiale.

            With .Range(.Cells(1, kk), .Cells(n, kk + 7))
                .Clear
                .Resize(, 2).Value = Tnp
                With .Font
                    .Name = "Arial": .Size = 7
                End With
                .VerticalAlignment = xlCenter
                .RowHeight = 9.25
                .Borders.Weight = xlThin

On se trouve toujours (depuis le début de la 2e partie) dans un bloc With référant à la feuille. On y insère un nouveau bloc With dans lequel la référence est ramenée à la plage occupée par la liste (à sa nouvelle taille).

[Pour l'anecdote, cibler la plage par redimensionnement à partir de la première cellule (avec .Resize...) provoquait une erreur 1004. Il arrive qu'Excel ne digère pas certains redimensionnement selon cette méthode... ce pourquoi elle est définie par les premières et dernières cellules.]

On commence par effacer la plage, tant contenu que mise en forme. Puis on affecte la nouvelle liste (tableau) sur les deux premières colonnes...

Le reste du code ci-dessus opère le rétablissement de la mise en forme commune : police Arial de taille 7, alignement vertical centré (pas dans la mise en forme initiale, mais améliore la lisibilité), hauteur de ligne à 9,25 points (elle était initialement à 9, avec 9,75 pour les lignes contenant des titres de classes, ce qui introduisait des irrégularités...), bordures en trait fin.

On va poursuivre en traitant la mise en forme des intitulés de classes :

                Application.DisplayAlerts = False
                For cl = 1 To UBound(Tcl, 2)
                    With .Rows(Tcl(1, cl))
                        .MergeCells = True
                        With .Font
                            .Name = "Calibri": .Bold = True
                        End With
                        .HorizontalAlignment = xlCenter
                        .Interior.Color = vbYellow
                        .Borders(xlEdgeTop).Weight = xlMedium
                        .Borders(xlEdgeBottom).Weight = xlMedium
                    End With
                Next cl
                Application.DisplayAlerts = True

L'inhibition des messages d'alerte vise à éviter le message signalant que la fusion ne conserve qu'une valeur... On fait une boucle sur les lignes concernées (en utilisant le tableau Tcl) : on procède à la fusion des cellules de la ligne, au changement de police en Calibri et à sa mise en gras, au centrage horizontal, à la coloration en jaune du fond, et au passage des traits haut et bas des cellules fusionnées en épaisseur médium.

                If gr <> "" Then
                    gr = Split(gr, ";")
                    For i = 1 To UBound(gr)
                        .Cells(CInt(gr(i)), 1).Resize(, 2).Interior.Color = RGB(217, 217, 217)
                    Next i
                End If
            End With

Enfin, si des noms doivent être grisés (test sur la valeur de gr), on splitte la variable sur le séparateur ";", le tableau résultant nous fournit les numéros de lignes à griser (à partir de l'indice 1) sur les deux première cellules, ce que l'on fait.

Ce qui termine la mise en forme.

Il reste à dater la mise à jour : la date figure en colonne B, 11 lignes en dessous de la 1re liste de la feuille. On l'insère donc lors du trairement de cette première liste.

            If kk = 1 Then .Range("B" & n + 11) = Date
        Next kk
    End With
End Sub

En espérant que mes explications te seront utiles...

Cordialement.