VBA to Hide Rows/Columns based on multiple Drop Downs

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
186
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,040
Office Version
  1. 365
Platform
  1. Windows
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
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
186
Brilliant - worked a treat (I did make some changes to what rows were visible etc when selected, but the code worked a treat)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,040
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,907
Members
412,689
Latest member
nhsmedic
Top