[resolu] liste dynamiques avec des cellules vides

merinos

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):eek:ffset(" + deptNom + ",200000,0)))"

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

  • dynrange and colors.zip
    213 KB · Affichages: 39
Dernière édition:

job75

XLDnaute Barbatruc
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+
 

job75

XLDnaute Barbatruc
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.
 

job75

XLDnaute Barbatruc
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+
 

job75

XLDnaute Barbatruc
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

  • dynrange and colors(1).xlsm
    26.8 KB · Affichages: 31
Dernière édition:

job75

XLDnaute Barbatruc
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

  • dynrange and colors(2).xlsm
    26.8 KB · Affichages: 34

merinos

XLDnaute Accro
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
 

merinos

XLDnaute Accro
Re : liste dynamiques avec des cellules vides

Salut Job75,

:cool:Très efficace ta macro.

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

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

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... :mad::mad::mad::mad::mad::mad:
 

Pièces jointes

  • dynrange 2.0.xlsm
    19.5 KB · Affichages: 33

merinos

XLDnaute Accro
Re : liste dynamiques avec des cellules vides

merci ,c'est super.

et voici mon application première: une recherche dynamique qui travaille par offset.
 

Pièces jointes

  • recherche dynamique.xlsm
    22.2 KB · Affichages: 28
  • recherche dynamique.xlsm
    22.2 KB · Affichages: 35
  • recherche dynamique.xlsm
    22.2 KB · Affichages: 38

merinos

XLDnaute Accro
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

  • recherche dynamique 2017 11 15.xlsm
    24.6 KB · Affichages: 17

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T