Przejdź do głównej zawartości

Jak ukryć lub odkryć kolumny na podstawie wyboru listy rozwijanej w programie Excel?

Korzystając z programu Excel, możesz ukrywać lub odkrywać określone kolumny na podstawie wyboru listy rozwijanej. Na przykład, jeśli wybierzesz Nie z rozwijanej listy, kolumny od C do I będą ukryte, ale jeśli wybierzesz Tak, ukryte kolumny od C do I zostaną odkryte. Zobacz poniższy zrzut ekranu.
W tym artykule pokażemy Ci metodę VBA do ukrywania lub odkrywania kolumn na podstawie wyboru listy rozwijanej w programie Excel.

Ukryj lub odkryj kolumny na podstawie wyboru listy rozwijanej w programie Excel


Ukryj lub odkryj kolumny na podstawie wyboru listy rozwijanej w programie Excel

Jak wspomniano w powyższym przykładzie, aby ukryć lub odkryć kolumny od C do I na podstawie wyboru listy rozwijanej, wykonaj następujące czynności.

1. Najpierw utwórz listę rozwijaną z Tak i Nie, których potrzebujesz.

2. Następnie naciśnij inny + F11 otworzyć Microsoft Visual Basic dla aplikacji okno.

3. Kliknij dwukrotnie nazwę aktualnie otwartego arkusza w Projekt VBA , aby otworzyć edytor kodu.

4. Następnie skopiuj i wklej poniższy kod VBA do edytora kodu.

Kod VBA: ukryj lub odkryj kolumny na podstawie wyboru z rozwijanej listy

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

Note: W powyższym kodzie Kolumna = 2 i Wiersz = 3 to odwołanie do komórki z rozwijanej listy, a zakres C: I to kolumny, które chcesz ukryć lub odkryć,. Zmień je zgodnie z potrzebami.

5. naciśnij inny + Q jednocześnie, aby wyjść z Microsoft Visual Basic dla aplikacji okno.

Odtąd po wybraniu Nie z listy rozwijanej wszystkie określone kolumny zostaną ukryte.

Ale jeśli wybierzesz Tak z listy rozwijanej, wszystkie ukryte kolumny zostaną natychmiast wyświetlone.


Podobne artykuły:

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
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