VBA to Hide Rows/Columns based on multiple Drop Downs

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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