boydcaroline73
New Member
- Joined
- Mar 29, 2021
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi all
I am trying to get 2 changes onto the same sheet. So I put a number into cell B2 and this hides/unhides rows based on that number. This code below works fine. I now need to add in a code to hide columns based on a number put into cell B3. eg 1 hides columns C and D, and 2 will just hide column D. Any help greatly appreciated.
I am trying to get 2 changes onto the same sheet. So I put a number into cell B2 and this hides/unhides rows based on that number. This code below works fine. I now need to add in a code to hide columns based on a number put into cell B3. eg 1 hides columns C and D, and 2 will just hide column D. Any help greatly appreciated.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B2"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "1": Rows("38:297").EntireRow.Hidden = True
Rows("14:37").EntireRow.Hidden = False
Case Is = "2": Rows("14:63").EntireRow.Hidden = False
Rows("66:297").EntireRow.Hidden = True
Case Is = "3": Rows("14:89").EntireRow.Hidden = False
Rows("92:297").EntireRow.Hidden = True
Case Is = "4": Rows("14:115").EntireRow.Hidden = False
Rows("118:297").EntireRow.Hidden = True
Case Is = "5": Rows("14:141").EntireRow.Hidden = False
Rows("144:297").EntireRow.Hidden = True
Case Is = "6": Rows("14:167").EntireRow.Hidden = False
Rows("170:297").EntireRow.Hidden = True
Case Is = "7": Rows("14:193").EntireRow.Hidden = False
Rows("196:297").EntireRow.Hidden = True
Case Is = "8": Rows("14:219").EntireRow.Hidden = False
Rows("222:297").EntireRow.Hidden = True
Case Is = "9": Rows("14:245").EntireRow.Hidden = False
Rows("248:297").EntireRow.Hidden = True
Case Is = "10": Rows("14:271").EntireRow.Hidden = False
Rows("274:297").EntireRow.Hidden = True
Case Is = "11": Rows("14:297").EntireRow.Hidden = False
End Select
End If
End Sub
Last edited by a moderator: