Masquer des lignes et colonnes en fonction d'une valeur d'un menu déroulant

Bonjour,

Après avoir réalisé mon fichier de base, un ami m'a permis de résoudre certaines fonctionnalités mais aujourd'hui j'ai besoin de votre aide pour tenter de le finaliser.

Voici une explication de ce fichier en espérant que ce soit compréhensible, n'hésitez pas à revenir vers moi si besoin.

Ce fichier se compose de 2 onglets :

- "Gestion des moyens" qui est une liste avec filtres de l'ensemble des véhicules du parc avec un identifiant unique en colonne H (masquée) et je modifie son status via les colonnes C à G. Le changement de status modifie la couleur et il en fait de même sur le second onglet.

- "Synoptique des moyens" :

- Regroupement des engins par lieu de garge (BEAUMONT /BOIS LE ROI / BOURRON...) en "ligne 17". La couleur se modifie donc en fonction des action sur l'onglet "Gestion des moyens".

- De B2 à B15 il s'agit de 15 villes (il y en aura 507 au total) que je complèterai si mon besoin est réalisable.

- En A17 c'est le menu déroulant de B2 à B15.

- Sur les lignes 2 à 15 il y a des chiffres dans les colonnes E à LA allant de 1 à 8 et qui permettent d'identifier les 8 garages les plus proches de la ville sélectionnée en A17, 1 étant le plus proche et 8 le plus éloigné.

Voici donc mon besoin pour cet onglet :

- Est il possible de créer un menu déroulant en A17 où je peux taper les premières lettres pour trouver plus vite ?

- Pour mon besoin principal du titre de ce post je vais mettre un exemple avec des données existantes pour que ce soit plus compréhensible :

Si je sélectionne "Avon" en A17 j'aimerais que s'affiche sur le range des lignes 2 à 15 uniquement la ligne 15 de la ville Avon.

En plus de cela je voudrais afficher sur le range des colonnes D à LA uniquement les colonnes où il y a un chiffre sur la ligne 15 de Avon.

Si je ne sélectionne aucune ville en A17 je souhaite que toutes le lignes de 2 à 15 soient masquées.

Pour que cela soit bien compréhensible je mets un onglet 3 qui n'est qu'une représentation visuelle du résultat souhaité.

Question subsidiaire qui serait l'apothéose en terme de résultat :

- Est il possible d'intégrer en plus dans tout ça un classement des colonnes avec les chiffres allant de 1 à 8 ? Je mets un onglet 4 qui n'est pareil qu'un représentation visuelle pour illustrer ma demande.

Merci d'avance pour votre aide.

Romain

Bonjour,

un premier point à modifier c'est de défusionner les données depuis A17 jusque la ligne 42.
Mettez Vos données en colonne B puis supprimez éventuellement la colonne A.
Il y a toujours cette manie de vouloir fusionner des cellules alors que ce n'est pas nécessaire. De plus VBA aime pas les fusions.

Après si vous ajoutez des villes je pense que l'idéal serait de nommer la plage entre la ligne 2 et la ligne 17, puis d'ajouter les nouvelles villes en les insérant dans cette plage. Cela éviterait de de devoir modifier le code à chaque ajout
Si ok, faites comme ceci :

Point 1
- Allez sur l"onglet "Synoptique des moyens"
- Allez dans le menu FORMULE -> Gestionnaire de noms
- Cliquez sur Nouveau et ajoutez le nom --> Villes
- Dans "fait référence à", ajoutez cette formule --> =DECALER($B$2;;;NBVAL($B:$B);-1)

Point 2
- Cliquez sur A17
- Allez dans le gestionnaire de nom
- Cliquez sur Nouveau et ajoutez le nom --> Choix_ville puis OK

Point 3
- Ensuite faites un click droite sur la feuille Synotique
- Choisir "Visualiser le code"
- Coller le code ci-dessous dans la fenêtre

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Choix_ville")) Is Nothing Then
    For Each cel In Range("villes")
        If cel <> Target.Value Then
            Rows(cel.Row).Hidden = True
        Else: Rows(cel.Row).Hidden = False
        End If
    Next cel

End If
End Sub

Faites un essai en changeant A17

Bonjour Dan,

Merci pour ton retour, voici les miens suite à tes propositions et mes tests :

- A l'origine il y avait des données dans les colonnes A et B d'où la fusion que j'ai donc supprimé car inutile en effet.

- J'ai oublié de préciser que de base les lignes 2 à 16 seront masquées, il ne s'affichera que la ligne de la ville sélectionnée en A17 qui est remontée d'un cran car j'en ai profité pour supprimer la ligne inutile entre les villes et la case du menu déroulant.

- Lorsque je sélectionne une ville ça m'affiche bien la ligne de la ville choisie mais ça masque aussi des lignes entre 18 et 35, chose que je ne veux pas.

- Je souhaitais également savoir si il est possible de n'afficher que les colonnes de la ligne sélectionnée.

Exemple avec ACHERES LA FORET : Est il possible de n'afficher que les colonnes où il y a les chiffres de 1 à 8 (H, J, M, O...) sur la ligne 3 ?

Merci pour ton aide,

Romain

Bonjour

- A l'origine il y avait des données dans les colonnes A et B d'où la fusion que j'ai donc supprimé car inutile en effet.

Ok c'est bon cela.

il ne s'affichera que la ligne de la ville sélectionnée en A17 qui est remontée d'un cran car j'en ai profité pour supprimer la ligne inutile entre les villes et la case du menu déroulant.

Non ce n'est pas bon cela. Voir ci-dessous

- Lorsque je sélectionne une ville ça m'affiche bien la ligne de la ville choisie mais ça masque aussi des lignes entre 18 et 35, chose que je ne veux pas.

Oui normal, car vous avez modifié votre fichier sur la colonne A et pas comme je vous avais écrit et les plages des noms ne sont plus correctes
1. Je ne vous ai pas écrit de supprimer la ligne vide entre la 16 et 17. Donc remettez-la.
2. Pourquoi commencez-vous sur la ligne 3 au lieu de 2 comme dans votre 1er fichier posté ?
3. Après il faut revérifier que les noms choix_Ville et et Villes sont ceux que je vous ai décrit dans mon post précédent car là les plages ne sont pas bonnes. Refaites exactement ce que j'ai écrit dans mon post précédent surtout

Au final vous devez avoir ceci et toujours laisser la ligne 16 vide

EDIT :
- après une chose que je n'ai pas écrite c'est la définition de votre liste de validation qui doit aller de $A$2 à $A$15 (raison de la ligne vide aussi)
- je ne sais pas votre application finale mais est-ce utile de mettre chaque fois le nom de votre ville en verticale juste avant chaque tableau sachant que vous l'avez en horizontal dans la ligne 17

Bonjour Dan,

Je pensais avoir fait attention au recopiage des infos transmises sur le premier mail mais finalement non.

Comme vous pouvez le voir le choix de la liste avec l'affichage de la ligne concernée fonctionne parfaitement bien.

Je vais peut être abuser de vos compétences mais est il possible de n'afficher que les colonnes comme expliqué précédemment ?

Encore merci pour m'avoir solutionné ce premier point.

Concernant votre question finale :

Les garages en ligne horizontale 17 sont tous rentrés au nombre de 60 environ mais concernant les communes je n'ai mis que les premières pour valider l'ensemble mais au final je vais avoir environ 500 communes.

Romain

Je vais peut être abuser de vos compétences mais est il possible de n'afficher que les colonnes comme expliqué précédemment ?

Oui c'est possible mais si vous pouviez répondre à mon point 2 dans l'Edit de mon post précédent afin que le code soit correct

NB : Nul besoin de me reposter votre fichier à chaque modification. J'ai le votre comme fichier de travail sur lequel j'applique les modifications proposées. On évite d'alourdir le forum inutilement.

Je me suis aperçu que j'avais oublié de vous répondre sur ce point là, j'ai donc modifié mon message précédent en même temps que vous m'écriviez.

Voici ma réponse à ce sujet :

Concernant votre question finale :

Les garages en ligne horizontale 17 sont tous rentrés au nombre de 60 environ mais concernant les communes je n'ai mis que les premières pour valider l'ensemble mais au final je vais avoir environ 500 communes.

Les garages en ligne horizontale 17 sont tous rentrés au nombre de 60 environ mais concernant les communes je n'ai mis que les premières pour valider l'ensemble mais au final je vais avoir environ 500 communes.

Ok pour la liste en ligne 17 mais cela ne répond pas à la question au sujet des noms de villes placés en vertical dans mon EDIT du post précédent

"je ne sais pas votre application finale mais est-ce utile de mettre chaque fois le nom de votre ville en verticale juste avant chaque tableau sachant que vous l'avez en horizontal dans la ligne 17"

En vertical il y aura la liste de toutes les communes du secteur concerné au nombre de 500 environ.

Sur la ligne 17 (BEAUMONT / BOIS LE ROI / BOURRON...) les garages avec l'état des véhicules attachés à ce dernier.

L'idée est :

- Si je ne sélectionne aucune ville en A17 de voir l'état global des véhicules dans les garages

- SI je sélectionne une ville en A17 de voir uniquement les 8 garages les plus proches (d'où les numéros sur la ligne concernée)

Est ce que cela répond à votre question ?

Lorsque vous reprenez une ligne d'un post qui vous répond, utilisez l'icone "Citation" (à droite de l'icone </>), ce sera plus clair à lire. J'ai corrigé dans votre post

Sur la ligne 17 (BEAUMONT / BOIS LE ROI / BOURRON...) les garages avec l'état des véhicules attachés à ce dernier.

Oui j'avais compris cela

- Si je ne sélectionne aucune ville en A17 de voir l'état global des véhicules dans les garages

Ok

- SI je sélectionne une ville en A17 de voir uniquement les 8 garages les plus proches (d'où les numéros sur la ligne concernée)

Ah là je n'avais pas compris ce point.
donc si je choisis ARVILLE (ligne 10 par exemple), je dois conserver les colonnes B à F, M à P et masquer les colonnes G à K, Q à AE.... tant que je n'ai pas une mention 8 dans cette ligne ?

Ah là je n'avais pas compris ce point.

donc si je choisis ARVILLE (ligne 10 par exemple), je dois conserver les colonnes B à F, M à P et masquer les colonnes G à K, Q à AE....

C'est encore plus subtile. Je ne souhaite conserver que les colonnes ayant un chiffre sur la ligne 10 soit C, E, M, O, AG, AI... Il doit y avoir 16 colonnes sélectionnées à chaque fois.

tant que je n'ai pas une mention 8 dans cette ligne

Je ne suis pas certain de bien comprendre l'importance du "mention 8" mais les chiffres comme vous pouvez le voir ne sont pas toujours dans le même ordre puisque c'est fonction de la distance entre le garage et la ville d'intervention, je ne sais pas si c'est assez clair pour vous.

Si vous regardez l'onglet 3 du fichier c'est représentatif du résultat souhaité avec Avon.

C'est encore plus subtile. Je ne souhaite conserver que les colonnes ayant un chiffre sur la ligne 10 soit C, E, M, O, AG, AI... Il doit y avoir 16 colonnes sélectionnées à chaque fois.
Je ne suis pas certain de bien comprendre l'importance du "mention 8" mais les chiffres comme vous pouvez le voir ne sont pas toujours dans le même ordre puisque c'est fonction de la distance entre le garage et la
ville d'intervention, je ne sais pas si c'est assez clair pour vous.

Oui j'avais compris mais je vous parle de 8 car vous en avez parlé et là on pouvait supposer que vous pourriez aller plus haut que 8. Donc si vous avez un nombre supérieur à 8 il faut masquer aussi. Vous comprenez ?

Je pense comprendre oui mais la numérotation va uniquement de 1 à 8 et il n'est pas envisagé d'aller plus haut.

Au fait les chiffres sont toujours placés sur les colonnes 1 et 3 au dessus du tableau. exemple en ligne 10 --> chiffre 1 sur colonne C et E ou on peut avoir des chiffres sur chacune des colonnes ?

Toujours sur les colonnes 1 et 3 en effet car ce sont les seules colonnes que je souhaite visible, les autres ont servies uniquement pour la création des véhicules.

Si je souhaite ne pas sélectionner de ville est ce que la formule que vous allez appliquez va fonctionner pour masquer les cellules ou bien je crée une ligne "Toutes" avec des 1 dans chaque colonne 1 et 3 des blocs pour afficher tous les garages mais uniquement les colonnes souhaitées. Qu'en pensez vous ?

Si je souhaite ne pas sélectionner de ville est ce que la formule que vous allez appliquez va fonctionner pour masquer les cellules ou bien je crée une ligne "Toutes" avec des 1 dans chaque colonne 1 et 3 des blocs pour afficher tous les garages mais uniquement les colonnes souhaitées. Qu'en pensez vous ?

Si vous ne sélectionnez pas de ville en A17, le code affichera toutes les lignes et colonnes de la feuille.

Voici le code à tester. Supprimez le code que je vous avais donné et remplacez-le par celui ci-dessous

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Choix_ville")) Is Nothing Then
    If Target = vbNullString Then
        With Cells
            .EntireRow.Hidden = False
            .EntireColumn.Hidden = False
        End With
        Exit Sub
    End If

    Dim cel As Range, plage As Range
    Dim dcol As Integer, i As Integer, lig As Integer

    dcol = ActiveSheet.UsedRange.Columns.Count
    lig = WorksheetFunction.Match(Target.Value, Range("Villes"))
    Set plage = Range(Cells(lig, 2), Cells(lig, dcol))

    On Error Resume Next
    'masquer affiche colonne
    Select Case WorksheetFunction.Sum(plage)
        Case Is > 0 'si on a un chiffre dans la ligne ville
            For i = 3 To dcol Step 5
                If WorksheetFunction.Sum(Range(Cells(lig, i - 1), Cells(lig, i + 4))) > 0 Then
                    Columns(i - 1).Resize(, 5).Hidden = False
                Else: Columns(i - 1).Resize(, 5).Hidden = True
                End If
            Next i

        Case Is = 0 'si pas de chiffre dans la ligne ville
            Columns(2).Resize(, dcol).Hidden = True
    End Select

    'masquer afficher villes
    For Each cel In Range("Villes")
        If cel <> Target.Value Then
            Rows(cel.Row).Hidden = True
        Else: Rows(cel.Row).Hidden = False
        End If
    Next cel
End If
End Sub

Si ok et terminé pensez à

Cordialement

Malheureusement il ne fonctionne pas chez moi, la sélection des colonnes est totalement anarchique, voulez vous que je vous envoie le fichier pour voir où je me suis trompé ?

Ok renvoyez le moi

La formule choix_Ville n'est pas celle que je vous ai donnée ici --> https://forum.excel-pratique.com/s/goto/1187626 -> point 1

Je vous la redonne en tenant compte de la ligne 1 qui reste vide --> =DECALER($A$1;;;NBVAL($A:$A);-1)

EDIT Oups désolé c'est la formule Villes qui n'est pas correcte. Choix ville c'est bon, cela pointe sur A17 uniquement

Re Edit : dans le code il faut ajouter une ligne juste en dessous de For Each cel In Range("Villes")

If cel.Row = Target.Row Then Exit Sub