انتقل إلى المحتوى الرئيسي

كيفية إخفاء أو إظهار الأعمدة بناءً على اختيار القائمة المنسدلة في Excel؟

أثناء استخدام Excel ، يمكنك إخفاء أعمدة معينة أو إظهارها بناءً على اختيار القائمة المنسدلة. على سبيل المثال ، إذا حددت لا في القائمة المنسدلة ، فسيتم إخفاء العمود C إلى I ، ولكن إذا حددت نعم ، فسيتم إخفاء الأعمدة المخفية C إلى I. انظر أدناه لقطة الشاشة المعروضة.
في هذه المقالة ، سوف نعرض لك طريقة VBA لإخفاء أو إظهار الأعمدة بناءً على تحديد القائمة المنسدلة في Excel.

إخفاء أو إظهار الأعمدة بناءً على تحديد القائمة المنسدلة في Excel


إخفاء أو إظهار الأعمدة بناءً على تحديد القائمة المنسدلة في Excel

كما هو مذكور أعلاه ، لإخفاء أو إظهار الأعمدة C إلى I بناءً على اختيار القائمة المنسدلة ، يرجى القيام بما يلي.

1. أولاً ، قم بإنشاء القائمة المنسدلة الخاصة بك بنعم ولا التي تحتاجها.

2. ثم اضغط قديم + F11 لفتح Microsoft Visual Basic للتطبيق نافذة.

3. انقر نقرًا مزدوجًا فوق اسم الورقة المفتوحة الحالي في ملف مشروع VBAP لفتح محرر الكود.

4. ثم انسخ والصق كود فبا أدناه في محرر الكود.

كود فبا: إخفاء الأعمدة أو إظهارها بناءً على اختيار القائمة المنسدلة

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

ملاحظات: في الكود أعلاه ، العمود = 2 والصف = 3 هو مرجع الخلية في القائمة المنسدلة ، والنطاق C: هو الأعمدة التي تريد إخفاءها أو إظهارها ، يرجى تغييرها حسب حاجتك.

5. صحافة قديم + Q مفاتيح في وقت واحد للخروج من Microsoft Visual Basic للتطبيق نافذة.

من الآن فصاعدًا ، عندما تحدد لا في القائمة المنسدلة ، يتم إخفاء جميع الأعمدة المحددة.

ولكن إذا حددت نعم في القائمة المنسدلة ، فسيتم عرض جميع الأعمدة المخفية على الفور.


مقالات ذات صلة:

أفضل أدوات إنتاجية المكتب

🤖 مساعد Kutools AI: إحداث ثورة في تحليل البيانات على أساس: التنفيذ الذكي   |  إنشاء التعليمات البرمجية  |  إنشاء صيغ مخصصة  |  تحليل البيانات وإنشاء الرسوم البيانية  |  استدعاء وظائف Kutools...
الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |   جولة بدون صيغة 
سوبر بحث: معايير متعددة VLookup    VLookup ذات القيمة المتعددة  |   VLookup عبر أوراق متعددة   |   بحث غامض ....
قائمة منسدلة متقدمة: إنشاء القائمة المنسدلة بسرعة   |  القائمة المنسدلة التابعة   |  قائمة منسدلة متعددة التحديد ....
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة  |  تبديل حالة رؤية الأعمدة المخفية  |  مقارنة النطاقات والأعمدة 
الميزات المميزة: التركيز على الشبكة   |  عرض تصميم   |   شريط الفورمولا الكبير    مدير المصنفات والأوراق   |  مكتبة الموارد (النص السيارات)   |  منتقي التاريخ   |  اجمع أوراق العمل   |  تشفير/فك تشفير الخلايا    إرسال رسائل البريد الإلكتروني عن طريق القائمة   |  سوبر تصفية   |   مرشح خاص (تصفية غامق / مائل / يتوسطه خط ...) ...
أفضل 15 مجموعة أدوات12 نص الأدوات (إضافة نص, إزالة الأحرف، ...)   |   +50 رسم الأنواع (مخطط جانت، ...)   |   40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد، ...)   |   19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار، ...)   |   12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة، ...)   |   7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم الخلايا، ...)   |   ... و اكثر

عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت.  انقر هنا للحصول على الميزة التي تحتاجها أكثر...

الوصف


يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 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