[resolu] liste dynamiques avec des cellules vides

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
merinos-BernardEtang

merinos-BernardEtang

XLDnaute Accro
Bonjour à tous,

J'emploie assez régulièrement des "named ranges"

Je cherche a créer des liste dynamiques... et j'y suis arrivé A CONDITION qu'il n'y ai pas d'élément vide dans le liste.
(ce qui est le cas dans le liste 'test 3' ou les eux dernières valeurs ne sont pas dans la liste.)

la partie que le fait:
ActiveWorkbook.Names.Add Name:=lenom, RefersTo:="=OFFSET(" + deptNom + ",1,0,COUNTA(offset(" + deptNom + ",1,0)😱ffset(" + deptNom + ",200000,0)))"
Cliquez pour agrandir...

Je vous joint un fichier avec la macro qui le fait... mais j'arrive pas à introduire quelque chose qui ressemblerait à ".end(xlup)" dedans. je sais qu'il va falloir changer toute l'instruction...


Merci de donner un petit coup de main.


Bernard
 

Pièces jointes

Dernière édition:
Trier par date Trier par votes
Re : liste dynamiques avec des cellules vides

Bonsoir merinos,

Tout dépend du but poursuivi.

S'il s'agit seulement de colorer des plages les nommer est totalement inutile :

Code: 
Sub Dynarange()
Dim dercel As Range
ActiveCell.EntireColumn.Interior.ColorIndex = xlNone
ActiveCell.Interior.ColorIndex = 46
Set dercel = Cells(Rows.Count, ActiveCell.Column).End(xlUp)
If dercel.Row > ActiveCell.Row Then _
  Range(ActiveCell(2), dercel).Interior.ColorIndex = 40
End Sub
A+
 
Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Bonjour merinos, Roland_M, le forum,

Avec un lien hypertexte sur la cellule active toujours pas besoin de nommer les plages :

Code: 
Sub Dynarange()
Dim dercel As Range
ActiveCell.EntireColumn.Hyperlinks.Delete
ActiveCell.EntireColumn.Interior.ColorIndex = xlNone
Set dercel = Cells(Rows.Count, ActiveCell.Column).End(xlUp)
If dercel.Row > ActiveCell.Row Then
  ActiveCell.Interior.ColorIndex = 46
  With Range(ActiveCell(2), dercel)
    .Interior.ColorIndex = 40
    ActiveSheet.Hyperlinks.Add ActiveCell, "", .Address
  End With
End If
End Sub
Bonne journée.
 
Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Re,

Quand la cellule active est vide le lien hypertexte insère l'adresse de la plage.

On peut l'effacer ensuite :

Code: 
Sub Dynarange()
Dim dercel As Range
With ActiveCell.EntireColumn
  .Hyperlinks.Delete
  .Interior.ColorIndex = xlNone
  .Replace "$*", "" 'efface l'adresse si elle existe
End With
Set dercel = Cells(Rows.Count, ActiveCell.Column).End(xlUp)
If dercel.Row <= ActiveCell.Row Then Exit Sub
ActiveCell.Interior.ColorIndex = 46
With Range(ActiveCell(2), dercel)
  .Interior.ColorIndex = 40
  ActiveSheet.Hyperlinks.Add ActiveCell, "", .Address
End With
End Sub
A+
 
Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Re,

Bon merinos, puisque vous êtes fana des "named Ranges" voyez le fichier joint et cette macro :

Code: 
Sub Dynarange()
With ActiveCell.EntireColumn
  '---noms définis---
  .Name = "Colonne" & .Column
  ActiveCell.Name = "Debut" & .Column
  ThisWorkbook.Names.Add "derlig" & .Column, _
    "=MAX(IF(ISNUMBER(MATCH(9^9,Colonne" & .Column & ")),MATCH(9^9,Colonne" & .Column & "))," _
    & "IF(ISNUMBER(MATCH(""zzz"",Colonne" & .Column & ")),MATCH(""zzz"",Colonne" & .Column & ")))"
  ThisWorkbook.Names.Add "Plage" & .Column, _
    "=OFFSET(Debut" & .Column & ",1,,derlig" & .Column & "-ROW(Debut" & .Column & "))"
  '---RAZ de la colonne---
  .Hyperlinks.Delete
  .Interior.ColorIndex = xlNone
  .Replace "Plage*", "" 'efface l'adresse si elle existe
  If Evaluate("derlig" & .Column) <= ActiveCell.Row Then Exit Sub
  '---lien hypertexte et couleurs---
  ActiveSheet.Hyperlinks.Add ActiveCell, "", "Plage" & .Column
  ActiveCell.Interior.ColorIndex = 46
  Range("Plage" & .Column).Interior.ColorIndex = 40
End With
End Sub
Edit : il y avait un lien hypertexte superflu qui trainait sur "yytf"...

A+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Bonjour merinos, le forum,

A partir d'Excel 2007 on peut alléger la formule de derligN avec SIERREUR :

Code: 
Sub Dynarange()
With ActiveCell.EntireColumn
  '---noms définis---
  .Name = "Colonne" & .Column
  ActiveCell.Name = "Debut" & .Column
  ThisWorkbook.Names.Add "derlig" & .Column, _
    "=MAX(IFERROR(MATCH(9^9,Colonne" & .Column & "),)," _
    & "IFERROR(MATCH(""zzz"",Colonne" & .Column & "),))"
  ThisWorkbook.Names.Add "Plage" & .Column, _
    "=OFFSET(Debut" & .Column & ",1,,derlig" & .Column & "-ROW(Debut" & .Column & "))"
  '---RAZ de la colonne---
  .Hyperlinks.Delete
  .Interior.ColorIndex = xlNone
  .Replace "Plage*", "" 'efface l'adresse si elle existe
  If Evaluate("derlig" & .Column) <= ActiveCell.Row Then Exit Sub
  '---lien hypertexte et couleurs---
  ActiveSheet.Hyperlinks.Add ActiveCell, "", "Plage" & .Column
  ActiveCell.Interior.ColorIndex = 46
  Range("Plage" & .Column).Interior.ColorIndex = 40
End With
End Sub
Fichier (2).

Bonne journée.

PS : je vais m'absenter pendant 3 jours.
 

Pièces jointes

Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Salut Job75,

Merci pour les fichiers... je suis de retour sur mon projet (vendredi on avait une activité avec la boite... )

Effectivement tu as pigé le concept de liste nommée dynamique. Et j'adore ta solution de couper le probleme en morceaux: on crée plusieurs parties qui changent dynamiquement... puis on les assemble. C'est fou comme on peut passer a coté des choses simples.

Je vais commencer par piger correctement ce que tu as placé dans tes fichiers.

à bientôt,


Bernard
 
Vote positif 0 Vote négatif
Re : liste dynamiques avec des cellules vides

Salut Job75,

😎Très efficace ta macro.

Juste une nuance: je ne peut la placer dans le fichier PERSONAL. Elle marche plus. 😕

Afin de bien comprendre ce que tu as fait, j'ai changé la façon dont les noms sont crées.
Si le titre de la liste est "ma liste", il est modifié en "ma_liste"
et les noms suivants sont crées:
ma_listestart : le point de depart de la liste avec son titre
ma_listelong : le nombre d'elements qui change de façon dynamique
ma_listecol : la colone
ma_liste : la liste dynamique

Sub dynarangeV()

Dim debut As Range
Dim nomrange As String

Set debut = ActiveCell.Cells(1, 1)

debut.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
nomrange = debut.Value

debut.Name = nomrange + "start" 'define the start point of the range
debut.EntireColumn.Name = nomrange + "col" 'name the column of the range
debut.EntireColumn.Interior.ColorIndex = xlNone
ThisWorkbook.Names.Add nomrange & "long", "=MAX(IF(ISNUMBER(MATCH(9^9," & nomrange & "col)),MATCH(9^9," & nomrange & "col)" _
& ",IF(ISNUMBER(MATCH(""zzz""," & nomrange & "col)),MATCH(""zzz""," & nomrange & "col))))"
ThisWorkbook.Names.Add nomrange, "=OFFSET(" & nomrange & "start,1,," & nomrange & "long -ROW(" & nomrange & "start))"
ActiveSheet.Hyperlinks.Add debut, "", nomrange
Range(nomrange & "col").Interior.ColorIndex = 40
debut.Interior.ColorIndex = 46

End Sub
Cliquez pour agrandir...

Quand je la fait tourner depuis le fichier PERSONAL, le range "ma_listelong" n'est pas crée... et bien quand on tourne depuis le fichier xlsm directement... 😡😡😡😡😡😡
 

Pièces jointes

Vote positif 0 Vote négatif
J'ai du corriger ma formule pour les named ranges... donc je re-publie ma spreadsheet.

Le nombre de lignes et de colonnes est dynamique, la recherche de donnée se fait sur base du nom de la colonne ....

je vous propose de changer en E6 "clé" par "qui".

Vous pouvez aussi ajouter une colonne dans les data et puis selectionner celle-ci en E6


Merinos
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

dionys0s
  • Question Question
VBA : Générer les noms des plages dynamiques automatiquement
Réponses
6
Affichages
2 K
Guest
G
M
[REF] Wiki 6 de MichelXld (Doublons, Tris, Filtres, Variables, Fichiers fermés, Acces
Réponses
0
Affichages
68 K
MichelXld
M
Retour