[Excel 2013] Liste déroulante sans cellules vide
Bonjour,
J'ai 2 feuilles dans mon classeur, une contenant tout mes items et une avec une colonne permettant de sélectionner des items de la la feuille 1 par le biais d'une liste déroulante.
Je souhaiterais que les listes déroulantes n'affichent pas les cellules vides.
La liste doit prendre toute la colonne de la feuille de données car celle ci est se rempli dynamiquement, les données changent donc en permanence.
Voici mon fichier: http://cjoint.com/?DKejjGwOhgK
Je vous remercie d'avance, j'avais trouvé quelques pistes avec mes recherches mais aucun exemple concret.
J'ai 2 feuilles dans mon classeur, une contenant tout mes items et une avec une colonne permettant de sélectionner des items de la la feuille 1 par le biais d'une liste déroulante.
Je souhaiterais que les listes déroulantes n'affichent pas les cellules vides.
La liste doit prendre toute la colonne de la feuille de données car celle ci est se rempli dynamiquement, les données changent donc en permanence.
Voici mon fichier: http://cjoint.com/?DKejjGwOhgK
Je vous remercie d'avance, j'avais trouvé quelques pistes avec mes recherches mais aucun exemple concret.
8 réponses
-
Contributeurou alors si vous voulez directement la mise à jour dans la feuille données de validation, voyez ce fichier
dés qu'une valeur est modifiée dans colonne A, la colonne est triée est mise à jour.
https://www.cjoint.com/?DKemzn9SKaz
Il ne reste plus qu'à nommer la colonne A avec cette formule de nom:
=DECALER('données de validation'!$A$2;;;NB.SI("données de validation'!$A:$A)-1)
et à utiliser le nom pour une validation où vous voulez
bonne route
-
ContributeurBonjour
une proposition ici avec une colonne de codage
https://www.cjoint.com/c/DKej6Xif0LG
sinon il y a possibilité, en VBA, de faire un copier coller de la liste et de la classer à chaque fois que vous modifier une valeur dans la liste initiale.
crdlmnt
-
Bonjour, bonjour Vaucluse, plus rapide
commencer par nommer la plage de tes items, exemple A2 à A100 nommée Item
écrire dans une colonne qui pourra être masquée exemple en H2 cette formule matricielle qu'il faudra confirmer en cliquant en même temps sur 3 touches du clavier (Ctrl, Shift et Entrée) et si tu fais bien la formule se placera entre {}
=SIERREUR(INDEX(Nom;PETITE.VALEUR(SI(Nom<>"";LIGNE(INDIRECT("1:"&LIGNES(Nom))));LIGNES($1:1)));"")
ensuite va dans le gestionnaire de noms /Nouveau, donne un nom au champ exemple Lit_Item et dans fait référence à colle cette formule
=DECALER('données de validation'!$H$1;;;NBVAL('données de validation'!$H:$H)-1)
ensuite va sur ta feuille à compléter en A2 et remplace ta liste de validation en faisant référence à ton champ
soit =List_Item
et incrémente vers le bas
https://www.cjoint.com/c/DKekmLQCkXD
évite de formater tes cellules inutilement sur toutes les lignes exemple tes bordures colonne A sur plus d'un million de cellules ce qui alourdi inutilement ton fichier
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir. -
ContributeurSalut Mike
et pour continuer, ... celui ci qui se met à jour chaque fois qu'on modfie une valeur dans la colonne d'origine
https://www.cjoint.com/?DKekqi9szcB
crdlmnt -
Merci pour vos réponse.
Je ne souhaite pas modifier le fichier avec les données, celui ci est généré par une application.
@Vaucluse: je ne comprends pas ce que tu as fait, il y a encore les blanc dans les listes déroulantes
@Mike-31: peut on nommer une colonne à la place d'une plage ? car le nombre de données change en permanence.
Quand on choisi la plage dans la validation des données pour une liste déroulante, n'y a t'il pas une formule sur une seule ligne (même compliquée) permettant de réaliser ma demande ? -
ContributeurVoyez avec Mike, donc....
.....mais pour ma part, je ne vois pas où sont les blancs dans ma liste déroulante si vous la basez sur la feuille "à complèter" de mes deux propositions
bonne chance
-
Re,
oui tout à fait, pour la plage Item, la formule est
='données de validation'!$A:$A
pour la plage List_Item il n'y a rien à changer, la plage s'adapte automatiquement à la longueur de la liste des formules
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir. -
Merci Vaucluse pour tes réponse et tes exemples, mais pourrais-tu s'il te plait m'expliquer comment tu as fait car je souhaiterais adapter ces solutions sur d'autres fichiers Excel.
-
ContributeurPour le dernier fichier:
__onglet développeur,
__visual basic à gauche
__dans la fenêtre clic sur le nom de la feuille pour voir le code de lancement
__clic sur module et "Class" pour voir le code d'exécution
__classeur 2: le code colle et trie sur la seconde feuille
__classeur 3 le code trie directement sur la colonne affectée dans le libellé
Suffit en suite d'ajuster les adresses dans les libellés pour adapter à d'autres cas
Notez quand même que je ne suis pas un grand spécialiste de VBA et que la macro Class est obtenue par enregistrement.Un professionnel pourrait sans doute faire ça plus élégamment
Si vous n'avez pas l'onglet développeur, afficher VBA avec les touches alt + F11
crdlmnt -
Encore merci, je vais finir par y arriver, enfin j'espère !
Je souhaite utiliser classeur2.
J'essais de comprendre mais voici mes questions:
1- Si j'ai bien compris, la tu copies la colonne originale ?:Sheets("données de validation").Select
Columns("A:A").Select
Selection.Copy
2- Ici tu copies dans la nouvelle colonne ?Sheets("à compléter").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
3- Et ça qu'est ce que c'est ?With ActiveWorkbook.Worksheets("à compléter").Sort
.SetRange Range("A:A")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Je n'arrive pas à reproduire ce que tu as fait dans un autre fichier, je modifie les valeurs dans les Range, ça copie/colle mais ça enlève pas les blancs. -
ContributeurLe qu'est ce que c'est, c'est le classement de la colonne collé
1: OK, c'est ça
2: colle sur la colonne A de la feuille "à complèter"
3: truie la colonne A de la feuille "à complèter"
Je viens de recharger le fichier et ça fonctionne, mais on est bien d'accord, le résultat est sur la feuille "à complèter" dans le classeur 2
Au cas où vous voudriez avoir le résultat dans une autre colonne sur la même feuille, il faut l'écrire comme ci dessous
(vous pouvez copier coller, les :lignes de textes explicatifs ne sont pas fonctionnelles et apparaitront en vert)
pour adapter à une autre colonne, changer simplement le "B:B" aux endroits voulus
crdlmnt
Sub Class()
Application.ScreenUpdating = False
'sélection de la feuille à traiter
Sheets("données de validation").Select
'copie de la colonne A
Columns("A:A").Select
Selection.Copy
'collage de A sur B
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Tri de la colonne B
Columns("B:B").Select
ActiveWorkbook.Worksheets("données de validation").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("données de validation").Sort.SortFields.Add Key:= _
Range("B2:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("données de validation").Sort
.SetRange Range("B1:B35")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
-
