메인 컨텐츠로 가기

Excel에서 드롭 다운 목록 선택을 기반으로 열을 숨기거나 숨김 해제하는 방법은 무엇입니까?

Excel을 사용하는 동안 드롭 다운 목록의 선택에 따라 특정 열을 숨기거나 숨김 해제 할 수 있습니다. 예를 들어, 드롭 다운 목록에서 아니요를 선택하면 C에서 I까지의 열이 숨겨 지지만 예를 선택하면 숨겨진 C에서 I까지의 열이 숨김 해제됩니다. 아래 스크린 샷을 참조하십시오.
이 기사에서는 Excel의 드롭 다운 목록 선택을 기반으로 열을 숨기거나 숨기는 VBA 방법을 보여줍니다.

Excel에서 드롭 다운 목록 선택에 따라 열 숨기기 또는 숨기기 해제


Excel에서 드롭 다운 목록 선택에 따라 열 숨기기 또는 숨기기 해제

위의 예에서 언급했듯이 드롭 다운 목록 선택을 기반으로 C에서 I 열을 숨기거나 숨김 해제하려면 다음과 같이하십시오.

1. 먼저 필요한 예 및 아니오로 드롭 다운 목록을 만듭니다.

2. 그런 다음 다른 + F11 를 열려면 응용 프로그램용 Microsoft Visual Basic 창.

3. 현재 열려있는 시트 이름을 두 번 클릭합니다. VBA 프로젝트 섹션을 클릭하여 코드 편집기를 엽니 다.

4. 그런 다음 VBA 코드 아래에 복사하여 코드 편집기에 붙여 넣습니다.

VBA 코드 : 드롭 다운 목록 선택에 따라 열 숨기기 또는 숨기기 해제

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

주의 사항: 위 코드에서 Column = 2 및 Row = 3은 드롭 다운 목록의 셀 참조이고 범위 C : I는 숨기거나 숨김 해제하려는 열입니다. 필요에 따라 변경하십시오.

5. 프레스 다른 + Q 나가기 위하여 동시에 열쇠 응용 프로그램용 Microsoft Visual Basic 창.

이제부터 드롭 다운 목록에서 아니요를 선택하면 지정된 모든 열이 숨겨집니다.

그러나 드롭 다운 목록에서 예를 선택하면 숨겨진 모든 열이 즉시 표시됩니다.


관련 기사 :

최고의 사무 생산성 도구

인기 기능: 중복 항목 찾기, 강조 표시 또는 식별   |  빈 행 삭제   |  데이터 손실 없이 열이나 셀 결합   |   수식없이 반올림 ...
슈퍼 조회: 다중 기준 VLookup    다중 값 VLookup  |   여러 시트에 걸친 VLookup   |   퍼지 조회 ....
고급 드롭다운 목록: 드롭다운 목록을 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭 다운 목록 ....
열 관리자: 특정 개수의 열 추가  |  열 이동  |  Toggle 숨겨진 열의 가시성 상태  |  범위 및 열 비교 ...
특색 지어진 특징: 그리드 포커스   |  디자인보기   |   큰 수식 바    통합 문서 및 시트 관리자   |  리소스 라이브러리 (자동 텍스트)   |  날짜 선택기   |  워크 시트 결합   |  셀 암호화/해독    목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터 (굵게/기울임꼴/취소선 필터링...) ...
상위 15개 도구 세트12 본문 도구 (텍스트 추가, 문자 제거,...)   |   50+ 거래차트 유형 (Gantt 차트,...)   |   40+ 실용 방식 (생일을 기준으로 나이 계산,...)   |   19 삽입 도구 (QR 코드 삽입, 경로에서 그림 삽입,...)   |   12 매출 상승 도구 (숫자를 단어로, 환율,...)   |   7 병합 및 분할 도구 (고급 결합 행, 셀 분할,...)   |   ... 그리고 더

Excel용 Kutools로 Excel 기술을 강화하고 이전과는 전혀 다른 효율성을 경험해 보세요. Excel용 Kutools는 생산성을 높이고 시간을 절약하기 위해 300개 이상의 고급 기능을 제공합니다.  가장 필요한 기능을 얻으려면 여기를 클릭하십시오...

kte 탭 201905


Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성을 50% 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
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