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.
A voir également:
Excel liste déroulante sans vide
Liste déroulante sans vide - Meilleures réponses
Excel liste déroulante ignorer si vide - Meilleures réponses
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.
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
é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.
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 ?
.....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
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.
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
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
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.
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
