[Excel 2013] Liste déroulante sans cellules vide [Fermé]

Signaler
-
Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
-
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.

8 réponses

Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
ou 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
2
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
Bonjour
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
Messages postés
17302
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
9 avril 2021
4 546
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.
Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
Salut 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 ?
Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
Voyez 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
Messages postés
17302
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
9 avril 2021
4 546
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.
Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
Pour 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.
Messages postés
25952
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
9 avril 2021
5 866
Le 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