Ga naar hoofdinhoud

Hoe kolommen te verbergen of zichtbaar te maken op basis van vervolgkeuzelijstselectie in Excel?

Terwijl u Excel gebruikt, kunt u specifieke kolommen verbergen of zichtbaar maken op basis van de selectie van een vervolgkeuzelijst. Als u bijvoorbeeld Nee selecteert in de vervolgkeuzelijst, worden kolom C tot en met I verborgen, maar als u Ja selecteert, worden de verborgen kolommen C tot en met I niet verborgen. Zie onderstaande screenshot getoond.
In dit artikel laten we u een VBA-methode zien om kolommen te verbergen of zichtbaar te maken op basis van vervolgkeuzelijstselectie in Excel.

Kolommen verbergen of zichtbaar maken op basis van keuzelijstselectie in Excel

Kolommen verbergen of zichtbaar maken op basis van keuzelijstselectie in Excel

Zoals hierboven vermeld, gaat u als volgt te werk om kolommen C tot I te verbergen of zichtbaar te maken op basis van de vervolgkeuzelijst.

1. Maak eerst uw vervolgkeuzelijst met Ja en Nee die u nodig heeft.

2. Druk vervolgens op anders + F11 om de te openen Microsoft Visual Basic voor toepassing venster.

3. Dubbelklik op de huidige geopende bladnaam in het VBA-project sectie om de code-editor te openen.

4. Kopieer en plak vervolgens onderstaande VBA-code in de code-editor.

VBA-code: kolommen verbergen of zichtbaar maken op basis van keuzelijst

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

Opmerking:: In de bovenstaande code zijn Column = 2 en Row = 3 de celverwijzing van de vervolgkeuzelijst, en het bereik C: I is de kolommen die u wilt verbergen of zichtbaar maken. Wijzig ze alstublieft naar uw behoefte.

5. druk op anders + Q toetsen tegelijkertijd om het Microsoft Visual Basic voor toepassing venster.

Vanaf nu, wanneer u Nee selecteert in de vervolgkeuzelijst, worden alle opgegeven kolommen verborgen.

Maar als u Ja selecteert in de vervolgkeuzelijst, worden alle verborgen kolommen onmiddellijk weergegeven.

Gerelateerde artikelen:

Beste Office-productiviteitstools

Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup  |   VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk   |  Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen   |  Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets:  12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools for Excelen ervaar efficiëntie als nooit tevoren. Kutools for Excel Biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

kte tabblad 201905

Office Tab Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Comments (83)
Rated 5 out of 5 · 1 ratings
Ian Balido
Ian Balido
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
Reply
0
0
crystal
crystal     Ian Balido
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
Reply
Report
0
0
Ian Balido
Ian Balido
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!
Reply
0
0
AntonyG
AntonyG
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
Reply
Report
0
0
AntonyG
AntonyG
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.
Reply
Report
0
0
Rated 5 out of 5
Jonathan
Jonathan
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
Reply
0
0
crystal
crystal     Jonathan
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
Reply
Report
0
0
Jonathan
Jonathan     crystal
This comment was minimized by the moderator on the site
Thanks for your help
Reply
0
0
Brian
Brian
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?
Reply
0
0
crystal
crystal     Brian
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
Reply
Report
0
0
zozamis
zozamis
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)
View Attachments (1 / 5)
Reply
Report
0
0
crystal
crystal     zozamis
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.
Reply
Report
1
0
zozamis
zozamis     crystal
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!
Reply
Report
0
0
crystal
crystal     zozamis
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
Reply
Report
0
0
zozamis
zozamis     crystal
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!!
Reply
Report
0
0
Rikki
Rikki
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".
Reply
0
0
ojnathalia
ojnathalia
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
Reply
Report
0
0
crystal
crystal     ojnathalia
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?
Reply
Report
0
0
bebf
bebf
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
Reply
Report
0
0
crystal
crystal     bebf
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.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations