Passer au contenu principal

Comment masquer ou afficher des colonnes en fonction de la sélection de la liste déroulante dans Excel?

Lorsque vous utilisez Excel, vous pouvez masquer ou afficher des colonnes spécifiques en fonction de la sélection d'une liste déroulante. Par exemple, si vous sélectionnez Non dans la liste déroulante, la colonne C à I sera masquée, mais si vous sélectionnez Oui, les colonnes masquées C à I seront non masquées. Voir la capture d'écran ci-dessous.
Dans cet article, nous allons vous montrer une méthode VBA pour masquer ou afficher des colonnes en fonction de la sélection de la liste déroulante dans Excel.

Masquer ou afficher les colonnes en fonction de la sélection de la liste déroulante dans Excel


Masquer ou afficher les colonnes en fonction de la sélection de la liste déroulante dans Excel

Comme indiqué dans l'exemple ci-dessus, pour masquer ou afficher les colonnes C à I en fonction de la sélection de la liste déroulante, procédez comme suit.

1. Tout d'abord, créez votre liste déroulante avec Oui et Non dont vous avez besoin.

2. Appuyez ensuite sur autre + F11 ouvrir le Microsoft Visual Basic pour Application fenêtre.

3. Double-cliquez sur le nom de la feuille ouverte actuelle dans le VBAProjet section pour ouvrir l'éditeur de code.

4. Ensuite, copiez et collez sous le code VBA dans l'éditeur de code.

Code VBA: masquer ou afficher les colonnes en fonction de la sélection de la liste déroulante

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
    If Target.Column = 2 And Target.Row = 3 Then
        If Target.Value = "No" Then
            Application.Columns("C:I").Select
            Application.Selection.EntireColumn.Hidden = True
        ElseIf Target.Value = "Yes" Then
            Application.Columns("C:I").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
End Sub

Notes: Dans le code ci-dessus, Column = 2 et Row = 3 est la référence de cellule de la liste déroulante, et la plage C: I est les colonnes que vous souhaitez masquer ou afficher, veuillez les modifier selon vos besoins.

5. presse autre + Q touches simultanément pour quitter Microsoft Visual Basic pour Application fenêtre.

À partir de maintenant, lorsque vous sélectionnez Non dans la liste déroulante, toutes les colonnes spécifiées sont masquées.

Mais si vous sélectionnez Oui dans la liste déroulante, toutes les colonnes masquées s'affichent immédiatement.


Articles Liés:

Meilleurs outils de productivité bureautique

🤖 Aide à l'IA Kutools: Révolutionner l'analyse des données en s'appuyant sur : Exécution intelligente   |  Générer du code  |  Créer des formules personnalisées  |  Analyser les données et générer des graphiques  |  Invoquer les fonctions Kutools...
Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Description


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (83)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
My dropdown has multiple options which are: Early convos, Mid-negotiations, Currently working, and Rejected. I want to two columns when the Early convos, Mid-negotiations, and Currently working options are selected and show the same two columns when Rejected is selected.

I would like to know how to code the If Target.Value = "Early convos, Mid-negotiations, Currently working" (multiple options).

My current code is below.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 7 And Target.Row = 3 Then
If Target.Value = "Early convos,Mid-negotiations,Currently working" Then
Application.Columns("H:I").Select
Application.Selection.EntireColumn.Hidden = True
ElseIf Target.Value = "Rejected" Then
Application.Columns("H:I").Select
Application.Selection.EntireColumn.Hidden = False
End If
End If
End Sub
This comment was minimized by the moderator on the site
Hi,
The following VBA code might help. Please give it a try.
Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated based on your requirements
    If Target.Column = 7 And Target.Row = 3 Then
        Select Case Target.Value
            Case "Early convos", "Mid-negotiations", "Currently working"
                Columns("H:I").EntireColumn.Hidden = True
            Case "Rejected"
                Columns("H:I").EntireColumn.Hidden = False
        End Select
    End If
End Sub
This comment was minimized by the moderator on the site
I used the original code that you posted and edited to fit my needs. However, my drop-down selections are not YES or NO. My choices are: Early convos, Mid-negotiations, Currently Working, and Rejected.

I want Column 13 Row 6 to be hidden when the choices selected are "Early convos, Mid-negotiations, Currently Working" and I want them hidden when the "Rejected" is selected.

How do I add multiple choices in: If Target.Value = "Early convos" and more choices on here?

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 13 And Target.Row = 6 Then
If Target.Value = "Early convos" Then
Application.Columns("N:O").Select
Application.Selection.EntireColumn.Hidden = True
ElseIf Target.Value = "Rejected" Then
Application.Columns("N:O").Select
Application.Selection.EntireColumn.Hidden = False
End If
End If
End Sub

I hope I explained it good and looking forward to your response.

I appreciate your time and assistance!
This comment was minimized by the moderator on the site
I made it

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xCells As String
xCells = "50:99" 'change this to the row numbers

If Target.Column = 8 And Target.Row = 10 And Target.Value = "No" Then
Application.Worksheets("DOCUMENT FORM").Rows(xCells).Hidden = "True"
Else
Application.Worksheets("DOCUMENT FORM").Rows(xCells).Hidden = "False"
End If

End Sub
This comment was minimized by the moderator on the site
Hello there,

This code worked worked but I wanted to hide "row 50:99" of another worksheet name: "Document Form"
I tried with below code but I'm missing something

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 And Target.Row = 7 Then
If Target.Value = "No" Then
Application.Worksheets("DOCUMENT FORM").Rows("50:99").Select
Application.Worksheets("DOCUMENT FORM").Selection.EntireRow.Hidden = True
Else
If Target.Value = "Yes" Then
Application.Worksheets("DOCUMENT FORM").Rows("50:99").Select
Application.Worksheets("DOCUMENT FORM").Selection.EntireRow.Hidden = False
End If
End If
End Sub

Please help.

Thanks in advance.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi,

I am trying to use this code twice in one sheet to reveal to different sets of rows based on two different cells. How do I make this work? The code I have is written as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 8 And Target.Row = 20 Then
If Target.Value = "No" Then
Application.Rows("21:24").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("21:24").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 8 And Target.Row = 37 Then
If Target.Value = "No" Then
Application.Rows("38:41").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("38:41").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub

Thank you in advance
This comment was minimized by the moderator on the site
Hi Jonathan,
Try the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220728
If Target.Column = 8 And Target.Row = 20 Then
If Target.Value = "No" Then
Application.Rows("21:24").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("21:24").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
If Target.Column = 8 And Target.Row = 37 Then
If Target.Value = "No" Then
Application.Rows("38:41").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("38:41").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub
This comment was minimized by the moderator on the site
Thanks for your help
This comment was minimized by the moderator on the site
Hej,

Jeg har forsøgt at bruge din VBA kodning til at skjule bestemte rækker i stedet for kolonner. Jeg vil dog gerne have den til at skjuler rækkerne, i forhold til definerede sektioner fx. "sekt1", grundet jeg har mange sektioner der variere i linje antal.

Jeg har forsøgt mig med følgende kode - dog uden held, og evnerne er sluppet op!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeName As String
RangeName = "sekt1"

If Target.Column = 2 And Target.Row = 9 Then
If Target.Value = "No" Then
Application.Rows("Sekt1").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("Sekt1").Select
Application.Selection.EntireRow.Hidden = False
End If
End If

End Sub

Kan du være behjælpelig her?
This comment was minimized by the moderator on the site
Hi,
Suppose the range name "sekt1" contains many rows and you want to hide or unhide them depending on the selection of the dropdown list.
The code you provided has been updated. Please give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220506
Dim RangeName As String
RangeName = "sekt1"

If Target.Column = 2 And Target.Row = 9 Then

    If Target.Value = "No" Then
        Application.Range("Sekt1").Select
        Application.Selection.EntireRow.Hidden = True

    ElseIf Target.Value = "Yes" Then
        Application.Range("Sekt1").Select
        Application.Selection.EntireRow.Hidden = False
    End If
End If

End Sub
This comment was minimized by the moderator on the site
Hi!

Great explanation, thanks!
I am very curious if it is possible to connect the drop-down list to specified cell entries, instead of a specified column range. That would make the sheet much more stable when adding new columns, since you won´t have to adapt the code every time a new column is added.

So in the current code the drop-down list is connected to a column range:

Application.Columns("H:K").Select

But would it be possible to let the code search for all columns where the e.g. the top row has a specific entry.
If I would select ´Brocolli´ in the drop down list, the code would show all the columns where Brocolli is written in a specific row (e.g. the top row could be dedicated to these entries)
This comment was minimized by the moderator on the site
Hi zozamis,I am a little confused about your question. Are your columns manually hidden beforehand and you only want to show the columns based on the top cell entry? When ´Brocolli´ is selected in the drop down list, the corresponding columns are displayed. If you switch to another item in the drop down list, just hide the same columns again?Can you to be more specific of your question? Thank you.
This comment was minimized by the moderator on the site
Hi Crystal, what you describe is indeed what I am after! :)
The script now hides/unhides based on a predefined column series (in this example C:I)
<div data-tag="quote">If Target.Value = "No" Then
Application.Columns("C:I").Select
Application.Selection.EntireColumn.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Columns("C:I").Select
Application.Selection.EntireColumn.Hidden = False
I would like to have a script that selects the columns based on the top cell entry, instead of a predefined column series.
As example: when I would select ´brocoli´ in the drop-down list, it would first hide all columns and then unhide all columns where the top cell entry is ´brocoli´, instead of unhiding a pre-defined column series.
So where the old code predefines a ´column series´ like (C:I), the new code would search for a specific to ´cell-entry´ like Brocoli 
By doing this, the script would still work fine when a new column is added in between, and it could also be easier when columns with a certain label are not in a consequent series.
Does that make sence? Thanks!
This comment was minimized by the moderator on the site
Hi zozamis,I am sorry for the late responding. The following VBA code can do you a favor. But it has a limitation that the drop-down list cell must be located in column A of the worksheet. And you need to manually change the drop-down list cell (A3) in the code to your own one. Hope I can help. 
<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220315
Dim xCRg As Range
Dim xURg As Range
Dim xStr As String
Dim xRg As Range
Dim xFnum As Integer
Dim xBolSU, xBolDA As Boolean
Dim xStr2 As String
Dim xBol As Boolean
Set xURg = ActiveSheet.UsedRange
Set xCRg = xURg.Columns
xStr2 = "Brocolli"
'The drop-down list cell must be located in column A
xStr = Range("A3").Value 'The cell containing the drop-down list
If xStr = xStr2 Then
xBol = False
Else
xBol = True
End If
On Error Resume Next
xBolSU = Application.ScreenUpdating
xBolDA = Application.DisplayAlerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For xFnum = 2 To xURg.Columns.Count
Set xRg = xURg.Columns.Item(xFnum)
If xRg.Cells.Item(1).Value = xStr2 Then
xRg.EntireColumn.Select
Application.Selection.EntireColumn.Hidden = xBol
Else
xRg.EntireColumn.Select
Application.Selection.EntireColumn.Hidden = Not xBol
End If
Next
Application.ScreenUpdating = xBolSU
Application.DisplayAlerts = xBolDA
End Sub
This comment was minimized by the moderator on the site
No sorry needed And this is amazing, I will implement this and let you know whether this works in my sheet!

Also, is it possible to apply the script to a given column range, so that some columns are not affecting by the ´hiding filter´
Any work-around to get the drop down in F4 instead of in the A column?

Thanks again!!
This comment was minimized by the moderator on the site
I am attempting to make a tracker for work to track the tasks that I have done. I am lost as to where to go for help but if you know where, or know of someone that can help with how to code I would appreciate the help. Please let me know if this is even possible. 
I have a dropdown in column E with the following selections: ER / SA / RQBased on dropdown list selection, I would like to HIDE the following rows: ER= Hide H-P | SA= Hide F-G & L-P | RQ= Hide F-K
In addition, I would also like to move completed items (Marked "Complete" in Column A) to either the bottom or to a new worksheet titled "Completed".
This comment was minimized by the moderator on the site
Hi any help
how to hide specific column using dropdown and select specific values or text
This comment was minimized by the moderator on the site
Hi,I don't get your point. This article demonstrates the method to hide columns based on the drop-down list selection. Would you try to be more specific about your issue?
This comment was minimized by the moderator on the site
I am using the code below to hide various columns depending on the selection from a drop-down box located in cell C3, but after a calculation is performed anywhere in the worksheet, ALL columns become UNHIDDEN. How do I fix this?

Private Sub Worksheet_Change(ByVal Target As Range)

Columns("D:F").AutoFit

Dim Proj1 As String
Dim Proj2 As String
Dim Proj3 As String
Dim Proj4 As String
Dim Proj5 As String
Dim Proj6 As String
Dim Proj7 As String
Dim Proj8 As String
Dim Proj9 As String
Dim Proj10 As String

Proj1 = ActiveWorkbook.Sheets("Projects").Range("A1").Value
Proj2 = ActiveWorkbook.Sheets("Projects").Range("A2").Value
Proj3 = ActiveWorkbook.Sheets("Projects").Range("A3").Value
Proj4 = ActiveWorkbook.Sheets("Projects").Range("A4").Value
Proj5 = ActiveWorkbook.Sheets("Projects").Range("A5").Value
Proj6 = ActiveWorkbook.Sheets("Projects").Range("A6").Value
Proj7 = ActiveWorkbook.Sheets("Projects").Range("A7").Value
Proj8 = ActiveWorkbook.Sheets("Projects").Range("A8").Value
Proj9 = ActiveWorkbook.Sheets("Projects").Range("A9").Value
Proj10 = ActiveWorkbook.Sheets("Projects").Range("A10").Value

Dim xRG As Range
Dim xHRow As Integer
Set xRG = Range("C3")
If Not Intersect(Target, xRG) Is Nothing Then

If Target.Value = Proj1 Then
Application.Columns("E:F").Hidden = True
Application.Columns("D").Hidden = False

ElseIf Target.Value = Proj2 Then
Range("D:D, F:F").EntireColumn.Hidden = True
Application.Columns("E").Hidden = False

End If
End If
End Sub
This comment was minimized by the moderator on the site
Hi,Can you attach your file here? I tried the code and did some calculations in the worksheet, but the columns are still hidden. We need more details to fix the problem. Sorry for the inconvenience.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations