hmltnangel
Active Member
- Joined
- Aug 25, 2010
- Messages
- 290
- Office Version
- 365
- Platform
- 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
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