Améliorer un redimensionnement de plage nommée

[GeoTrouvePas]

Bonjour tout le monde,

Afin de prendre en compte le rajout d'une ou plusieurs lignes dans un plage nommée, je me suis fait la macro suivante qui redimensionne la plage, la colore correctement, refait les bordures etc...

Sub Actualiser_Plage(Plage_A_Actualiser As Range, Nom_Plage As String)
    With Plage_A_Actualiser
        On Error Resume Next
        'Efface le nom
        .Name.Delete
        'Remet la couleur de fond
        .Cells.Interior.Color = RGB(102, 102, 53)
        'Compte le nombre de lignes
        Compteur = 1
        While .Item(Compteur + 1, 1) <> "" And .Item(Compteur + 2, 1) <> ""
            Compteur = Compteur + 1
        Wend
        'Renomme, colore et refait les bordures du tableau
        With .Resize(Compteur, .Columns.Count)
            .Name = Nom_Plage
            .Interior.Color = RGB(255, 255, 255)
        End With
        'Refait les bordures de l'entête du tableau
        With .Resize(1, .Columns.Count)
            '+ Gestion des bordures
        End With
    End With
End Sub

Cette macro fonctionne à peu près mais j'aurai aimé l'améliorer un petit peu car elle pose quelques soucis :

1) Même en utilisant une plage nommée en argument, je suis obligé de rajouter le nom de la plage en second argument car je n'arrive pas à le récupérer
2) J'ai été obligé de rajouter ".Name.Delete" au début sinon le redimensionnement ne fonctionnait pas systématiquement.
3) Je suis obligé d'employer une gestion d'erreur car, lorsque j'utilise une référence absolue en argument, la macro plante sur le Name.Delete

Auriez vous une ou deux idées pour rendre ce code un peu plus propre ?

Je vous remercie par avance de votre attention.

Bonne journée !

 

[Guest]

Bonjour,

Ne fonctionnera qu'avec des plages préalablement nommées!

Sub Actualiser_Plage(Plage_A_Actualiser As Range)
    Dim nm As Name
     With Plage_A_Actualiser
        Set nm = .Name
         'Remet la couleur de fond
         .Cells.Interior.Color = RGB(102, 102, 53)
         'Compte le nombre de lignes
         Compteur = 1
         While .Item(Compteur + 1, 1) <> "" And .Item(Compteur + 2, 1) <> ""
             Compteur = Compteur + 1
         Wend
         'Renomme, colore et refait les bordures du tableau
         With .Resize(Compteur, .Columns.Count)
             nm.RefersTo = "=" & .Parent.Name & "!" & .Address
             .Interior.Color = RGB(255, 255, 255)
         End With
         'Refait les bordures de l'entête du tableau
         With .Resize(1, .Columns.Count)
             '+ Gestion des bordures
         End With
     End With
 End Sub

Je ne comprends pas trop ton compteur.

A+

 

[GeoTrouvePas]

Re : Re: Améliorer un redimensionnement de plage nommée

Ne fonctionnera qu'avec des plages préalablement nommées!

C'est déjà ça de pris ! Merci beaucoup pour ton aide !

Je ne comprends pas trop ton compteur.

J'avoue que moi non plus ! ^^
Dans la précipitation, j'ai mis "And" à la place d'un "Or". En fait mon tableau peut éventuellement comporter une ligne vide. Je voulais donc arrêter le comptage que lorsque je rencontre deux lignes vides successives.

 

[Guest]

Re,

Je voulais donc arrêter le comptage que lorsque je rencontre deux lignes vides successives

Serais-tu dans une situation où tu ne peux pas employer la méthode End(xlup) ?

A+

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Re,

Effectivement je risque d'avoir plusieurs tableaux les uns en dessous des autres. Compte tenu de cet élément et du fait que je puisse éventuellement avoir une ligne vide de temps en temps, j'ai préféré ne pas utiliser End(xlUp) ou End(xlDown).

J'ai eu tort ?

Disons que j'aime bien faire des fontions donc je pourrais facilement me resservir dans d'autres applications. J'essaye donc de prendre en compte le maximum de contraintes possibles. La version présentée ici est allégée car normalement il y a aussi un compteur pour les colonnes. Mais j'ai préféré la simplifier au maximum pour faire un exemple clair.

 

[Guest]

re,

J'ai eu tort ?

Non,non, il y a des cas où effectivement, on ne peut pas se servir la méthode .End

A+

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Bon, je suis rassuré. J'aime pas utiliser des trucs tordus pour rien.
Merci pour ton aide et bonne fin d'après midi !

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Problème !!!!!
J'ai essayé la petite macro que tu as modifiée et je dois dire qu'Excel a un comportement plus que bizarre.

Le code s'exécute sans problème mais au final, la plage n'est plus nommée. Enfin disons que le nom de la plage n'est plus utilisable mais que la plage nommée semble toujours exister. En effet, après exécution du code, lorsque je la sélectionne, le nom n'apparaît pas dans le petit champ de la barre de forumule. Lorsque je me sers de cette plage nommée dans une macro, ça plante. Par contre quand je fais Insertion / Nom / Définir, le nom de la plage appraît toujours avec la bonne référence de cellules (voir capture ci - jointe). Plutôt bizarre non ?

 

[Guest]

Re : Améliorer un redimensionnement de plage nommée

Bonjour GeoTrouvePas,

Tu sais sans fichier exemple, on va tourner en rond. Sans doute le sais-tu au bout de 422 messages!

Pourquoi tu ne te fais pas une plage nommée avec la fonction décalée.
Si tu ne sais pas faire, vois dans ma signature le lien vers le petit utilitaire que j'a écrit pour ça.

A+

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Tu sais sans fichier exemple, on va tourner en rond. Sans doute le sais-tu au bout de 422 messages!

Et PAF dans ma trogne celle là ! Tu as tout à fait raison.

En fait j'ai en avais préparé et je me suis rendu compte, avant de poster, qu'une liste des 36 000 communes françaises, ben ça prenait pas mal de place ! ^^

Du coup je l'ai réduite et comme par hasard, ça fonctionne de nouveau. Du coup j'étais en train de chercher si il n'y a pas une taille critique à partir de laquelle ça fait tout déconner.

Pourquoi tu ne te fais pas une plage nommée avec la fonction décalée.
Si tu ne sais pas faire, vois dans ma signature le lien vers le petit utilitaire que j'a écrit pour ça.

A+

 

[Guest]

Re : Améliorer un redimensionnement de plage nommée

Re,

Du coup j'étais en train de chercher si il n'y a pas une taille critique à partir de laquelle ça fait tout déconner.

Ben normalement non! Mais il y a tellement de cas uniques que????

Mais je ne sais toujours pas pourquoi tu n'utilises pas un nom dynamique avec la fonction Decaler()

A+

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Oups désolé. Je suis tellement débordé aujourd'hui que ma mémoire de poisson rouge a du mal à suivre !

En fait je voulais me faire une procédure "générique" que je puisse utiliser tout au long de mon application à chaque fois que je rajoute une ligne dans une de mes tables (j'en ai des dizaines).

J'aliment ces tables à partir d'une saisie gérée par userform. Je voulais donc tout gérer par du vba et ne pas utiliser de formules.

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Eureka !!! J'ai trouvé !!!

Il manquait juste un petit "détail" dans la macro. A savoir les deux petits guillements de la ligne :

nm.RefersTo = "='" & .Parent.Name & "'!" & .Address

Une fois ce rajout effectué, ça marche impeccable.

Par contre je comprends pas pourquoi ça a marché quand j'ai réduit mon fichier tout à l'heure. J'ai du faire une fausse manip qui m'a fait croire que ça marchait ^^

Edit : Pour ceux qui voudrait tester ou se servir de cette procédure, je vous mets un petit fichier exemple.

Merci beaucoup pour ton aide Hasco !

 

[Guest]

Re : Améliorer un redimensionnement de plage nommée

Re,

Ah oui les ' sont indispensables pour les noms de feuille contenant des espaces!!!!!!

Le diable se cache dans les détails😱

A+

 

[GeoTrouvePas]

Re : Améliorer un redimensionnement de plage nommée

Le diable se cache dans les détails😱

Mdr. En l'occurence c'est toi le diable dans cette histoire ! Tu as réussi à me tenter en me proposant une belle macro si bien écrite, si facile à utiliser... Et moi j'ai cédé à la diabolique tentation ! Si j'avais su que c'était un casse tête déguisé qui allait me rendre fou pendant toute une après midi ! Diablerie d'apostrophe !

Bon trêve de plaisanterie, je te remercie sincèrement pour ton aide.

@+ et bonne fin de journée !