[Excel 2013] Liste déroulante sans cellules vide
Fermé
NoobExcel
-
4 nov. 2014 à 09:10
Vaucluse
Vaucluse
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
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
- Comment exclure des cellules vides d'une liste? ✓ - Forum - Excel
- [Excel 2013] Liste déroulante sans cellules vide - Forum - Excel
- Effacer cellule vide dans une liste déroulante - Forum - Excel
- Liste deroulante sans vide ✓ - Forum - Bureautique
- Liste déroulante avec cellules vides par formule ✓ - Forum - Excel
8 réponses
Vaucluse
4 nov. 2014 à 12:23
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 12:23
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
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
Vaucluse
4 nov. 2014 à 09:55
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 09:55
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
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
Mike-31
Modifié par Mike-31 le 4/11/2014 à 10:11
- Messages postés
- 17975
- Date d'inscription
- dimanche 17 février 2008
- Statut
- Contributeur
- Dernière intervention
- 27 juin 2022
Modifié par Mike-31 le 4/11/2014 à 10:11
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.
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.
Vaucluse
4 nov. 2014 à 10:12
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 10:12
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
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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 ?
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 ?
Vaucluse
4 nov. 2014 à 11:45
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 11:45
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
.....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
Mike-31
Modifié par Mike-31 le 4/11/2014 à 12:00
- Messages postés
- 17975
- Date d'inscription
- dimanche 17 février 2008
- Statut
- Contributeur
- Dernière intervention
- 27 juin 2022
Modifié par Mike-31 le 4/11/2014 à 12:00
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.
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.
Vaucluse
4 nov. 2014 à 16:46
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 16:46
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
__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 ?:
2- Ici tu copies dans la nouvelle colonne ?
3- Et ça qu'est ce que c'est ?
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.
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.
Vaucluse
4 nov. 2014 à 18:04
- Messages postés
- 26495
- Date d'inscription
- lundi 23 juillet 2007
- Statut
- Contributeur
- Dernière intervention
- 8 avril 2022
4 nov. 2014 à 18:04
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
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