VBA to Hide Rows/Columns based on multiple Drop Downs

H

hmltnangel

Active Member
Joined
Aug 25, 2010
Messages
290
Office Version
  1. 365
Platform
  1. Windows
Morning All.

I have pieced together the VBA below to allow Columns/Rows to be hidden based on the drop downs selected in C2/C3

It works fine if C3 is selected first then C2. But If I select an option in C2 then C3, the Column hiding for C3 doesn't work. Any ideas? Or even a slicker version of the same thing would be good

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

        
    If Target.Column = 3 And Target.Row = 3 Then
        If Target.Value = "" Then
            Application.Columns("F:BA").Select
            Application.Selection.EntireColumn.Hidden = False
        ElseIf Target.Value = "Business Acumen" Then
            Application.Columns("O:BA").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("F:N").Select
            Application.Selection.EntireColumn.Hidden = False
        ElseIf Target.Value = "Managing Self" Then
            Application.Columns("U:BA").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("F:N").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("O:T").Select
            Application.Selection.EntireColumn.Hidden = False
        ElseIf Target.Value = "Managing and Leading Others" Then
            Application.Columns("AA:BA").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("F:T").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("U:Z").Select
            Application.Selection.EntireColumn.Hidden = False
        ElseIf Target.Value = "Programme Management" Then
            Application.Columns("AJ:BA").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("F:Z").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("AA:AJ").Select
            Application.Selection.EntireColumn.Hidden = False
        ElseIf Target.Value = "Software" Then
            Application.Columns("BA").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("F:AJ").Select
            Application.Selection.EntireColumn.Hidden = True
            Application.Columns("AK:AZ").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
    
    
    If Intersect(Target, Range("C2")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub

    ElseIf Range("C2").Value = "" Then
        Rows("7:286").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Proj & Prog Management" Then
        Rows("18:286").EntireRow.Hidden = True
        Rows("7:17").EntireRow.Hidden = False

    ElseIf Range("C2").Value = "Bus Dev Mktg & Sales" Then
        Rows("7:17").EntireRow.Hidden = True
        Rows("37:286").EntireRow.Hidden = True
        Rows("18:37").EntireRow.Hidden = False

    ElseIf Range("C2").Value = "Finance" Then
        Rows("7:37").EntireRow.Hidden = True
        Rows("69:286").EntireRow.Hidden = True
        Rows("38:68").EntireRow.Hidden = False

    ElseIf Range("C2").Value = "Contracts Pricing & Procurement" Then
        Rows("7:68").EntireRow.Hidden = True
        Rows("90:286").EntireRow.Hidden = True
        Rows("69:89").EntireRow.Hidden = False

    ElseIf Range("C2").Value = "Software1" Then
        Rows("7:89").EntireRow.Hidden = True
        Rows("130:286").EntireRow.Hidden = True
        Rows("90:129").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "HR" Then
        Rows("7:129").EntireRow.Hidden = True
        Rows("145:286").EntireRow.Hidden = True
        Rows("30:144").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Admin" Then
        Rows("7:144").EntireRow.Hidden = True
        Rows("157:286").EntireRow.Hidden = True
        Rows("145:156").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Leadership" Then
        Rows("7:156").EntireRow.Hidden = True
        Rows("162:286").EntireRow.Hidden = True
        Rows("157:161").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Business Process & Planning" Then
        Rows("7:161").EntireRow.Hidden = True
        Rows("168:286").EntireRow.Hidden = True
        Rows("162:167").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Comms" Then
        Rows("7:167").EntireRow.Hidden = True
        Rows("174:286").EntireRow.Hidden = True
        Rows("168:173").EntireRow.Hidden = False
 
    ElseIf Range("C2").Value = "Industrial Operations" Then
        Rows("7:173").EntireRow.Hidden = True
        Rows("178:286").EntireRow.Hidden = True
        Rows("174:177").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "IT" Then
        Rows("7:177").EntireRow.Hidden = True
        Rows("242:286").EntireRow.Hidden = True
        Rows("178:241").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Interns" Then
        Rows("7:241").EntireRow.Hidden = True
        Rows("245:286").EntireRow.Hidden = True
        Rows("242:244").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Logistics Services" Then
        Rows("7:244").EntireRow.Hidden = True
        Rows("253:286").EntireRow.Hidden = True
        Rows("245:252").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Secure" Then
        Rows("7:252").EntireRow.Hidden = True
        Rows("260:286").EntireRow.Hidden = True
        Rows("253:259").EntireRow.Hidden = False
    
    ElseIf Range("C2").Value = "Other" Then
        Rows("7:259").EntireRow.Hidden = True
        Rows("269:286").EntireRow.Hidden = True
        Rows("260:268").EntireRow.Hidden = False
End If
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Not sure why you are getting problems, as it works for me, but try
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C3" Then
      Columns("F:BA").Hidden = True
      Select Case Target
         Case ""
            Columns("F:BA").Hidden = False
         Case "Business Acumen"
            Columns("F:N").Hidden = False
         Case "Managing Self"
            Columns("O:T").Hidden = False
         Case "Managing and Leading Others"
            Columns("U:Z").Hidden = False
         Case "Programme Management"
            Columns("AA:AJ").Hidden = False
         Case "Software"
            Columns("AK:AZ").Hidden = False
      End Select
   ElseIf Target.Address(0, 0) = "C2" Then
      Rows("7:286").Hidden = True
      Select Case Target.Value
         Case ""
            Rows("7:286").Hidden = False
         Case "Proj & Prog Management"
            Rows("7:17").Hidden = False
         Case "Bus Dev Mktg & Sales"
            Rows("18:37").Hidden = False
         Case "Finance"
            Rows("38:68").Hidden = False
         Case "Contracts Pricing & Procurement"
            Rows("69:89").Hidden = False
         Case "Software1"
            Rows("90:129").Hidden = False
         Case "HR"
            Rows("30:144").Hidden = False
         Case "Admin"
            Rows("145:156").Hidden = False
         Case "Leadership"
            Rows("157:161").Hidden = False
         Case "Business Process & Planning"
            Rows("162:167").Hidden = False
         Case "Comms"
            Rows("168:173").Hidden = False
         Case "Industrial Operations"
            Rows("174:177").Hidden = False
         Case "IT"
            Rows("178:241").Hidden = False
         Case "Interns"
            Rows("242:244").Hidden = False
         Case "Logistics Services"
            Rows("245:252").Hidden = False
         Case "Secure"
            Rows("253:259").Hidden = False
         Case "Other"
            Rows("260:268").Hidden = False
      End Select
   End If
End Sub
 
Upvote 0
Brilliant - worked a treat (I did make some changes to what rows were visible etc when selected, but the code worked a treat)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
803
Peter_SSs
Peter_SSs
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
632
John_w
J
R
  • Question Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
396
rgree05
R
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top