Nommer une zone dynamique classique ou sous forme de tableau en VBA

[Gudo]

Bonjour

J'essaye de réaliser une macro pour nommer une plage de cellules qui peut être classique ou sous forme de tableau
Ces plages s'agrandissent au fil du temps pas le bas.

Pour plus de clarté, j'ai réalisé un petit exemple avec des précisions complémentaires

Le sujet à peut être été déjà étudié, mais je n'ai pas trouvé
Merci d'avance pour votre aide

 

[BenHarber]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour Gudo,
Je pense qu'une macro n'est pas nécessaire pour réaliser ce que tu veux faire : tu peux en effet passer par la fonction décaler dans la définition de ton nom de plage.
cf. par exemple ce tuto.
Nommer une Plage Dynamique - Tutoriels & Astuces Excel > Fonctions & Formules - Tutoriels : myDearFriend! Excel Pages.

@+
BH

 

[JCGL]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour à tous,

Bien d'accord avec BenHarber : nommer la plage et s'en servir en VBA

Option Explicit


Sub Test1()
Dim DerL&, Zone As Range
DerL = Feuil1.Range("B" & Rows.Count).End(3).Row
Set Zone = Feuil1.Range("B5:F" & DerL)
Zone.Select
End Sub


Sub Test2()
Range("Zone1").Select
End Sub

A+ à tous

 

[job75]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour Gudo, BenHarber, Jean-Claude,

L'inconvénient de la fonction DECALER c'est qu'elle est volatile, donc recalculée à l'ouverture du fichier.

De ce fait il y a une invite à la fermeture même si l'on ne touche à rien.

Cela peut agacer certains, alors effectivement on peut l'éviter par VBA.

Mais en VBA il n'est pas nécessaire de nommer le tableau, il suffit de le définir par une variable.

Voyez le fichier joint et cette macro :

Sub DefinirTableau()
'touches Ctrl+A pour lancer la macro
Dim PremLig As Range, Tableau As Range
Set PremLig = [B5:F5] '1ère ligne du tableau, à adapter
Set Tableau = Range(PremLig, IIf(IsEmpty(PremLig(2, 1)), PremLig, PremLig(1).End(xlDown)))
MsgBox Tableau.Address(0, 0) 'pour tester
End Sub

A+

 

[Gudo]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour BenHarber

Merci pour ce lien, je ne connaissais pas cette technique

Après plusieurs essais, je vois tout l’intérêt de cette technique
Si je suis parvenu facilement à l'appliquer à la zone B5:B20 de mon exemple
Je n’arrive pas du tout à l'appliquer à la zone B5:F20 ???

C'est toujours et seulement la zone B5:B20 qui est validée


Bonjour JCGL

Merci aussi pour votre aide

J'ai bien essayé la formule

=DECALER('Exemple N°1'!$B$5:$B$168;;;NBVAL('Exemple N°1'!$B$5:$B$168))

Mais cela fait la même chose que

=DECALER('Exemple N°1'!$B$5;;;NBVAL('Exemple N°1'!$B:$F)-1;1)

ou que

=DECALER('Exemple N°1'!$B$5;;;NBVAL('Exemple N°1'!$B:$B)-1;1)

Que j'ai réalisé en suivant le tuto que m'a donné BenHarber

Par contre pour la macro Test1
Ce n'est pas ce que je demandais
Mais cela me parvient parfaitement

J'aimerais bien quand même comprendre cette technique de zone dynamique
Car je dois faire quelque chose de travers ???

A+

 

[JCGL]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour à tous,

Il m"étonnerait que tes deux dernières formules donnent le même résultat sauf si en E et F les cellules sont vides.

A+ à tous

 

[Gudo]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Bonjour Job75

Merci pour cette excellente macro !
Du coup c'est extrêmement simple et rapide à paramétrer

Par contre je ne comprends pas

De ce fait il y a une invite à la fermeture même si l'on ne touche à rien.

Re JCGL

Effectivement ce n'est pas la même chose
En fait j'obtiens une hauteur de ma sélection qui est un multiple de 5
Cette formule est stupide, mais entre temps j'ai compris, peut être pas tout ?
Mais j'ai trouvé le bonne formule

=DECALER('Exemple N°1'!$B$5;;;NBVAL('Exemple N°1'!$B:$B)-1;5)

Par contre la 1ère formule (la votre) et la 3ème ont bien le même résultat


A+

 

[job75]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Re,

Par contre je ne comprends pas :
"De ce fait il y a une invite à la fermeture même si l'on ne touche à rien."

Nommez un tableau comme BenHarber et JCGL vous l'on dit, ouvrez et fermez le fichier...

A+

 

[Gudo]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Re,

C'est bien ce que j'avais cru comprendre
Je viens de le refaire
Mais il ne passe rien à la fermeture

Je joins le fichier au cas où je n'aurais pas bien compris ce que vous voulez dire

A+

 

[job75]

Re : Nommer une zone dynamique classique ou sous forme de tableau en VBA

Re,

Oui pardon, si le nom défini par DECALER n'est pas utilisé dans une feuille il n'y a pas d'invite à la fermeture.

Si on l'utilise par exemple comme en H14 avec =SOMME(zone1) il y a une invite.

Fichier joint.

A+