BigRusty82
New Member
- Joined
- Jan 28, 2021
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi, this is my first post and my first venture into VBA and whilst I am learning VBA I would apricate some help with the following situation.
I want to hide rows in this worksheet based on values within a cell, whilst I can make this work for one set, i know that I cant just repeat this for the others as it gives an error. This is the code below with named ranges:
The NUM_AUTH and AUTH_x will change based on the value lookup and rows i want to hide. This I need to happen 4 times on one sheet.
Thanks
Rusty
I want to hide rows in this worksheet based on values within a cell, whilst I can make this work for one set, i know that I cant just repeat this for the others as it gives an error. This is the code below with named ranges:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("NUM_AUTH"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "1": Range("AUTH_2,AUTH_3,AUTH_4,AUTH_5,AUTH_6").EntireRow.Hidden = True
Range("AUTH_1").EntireRow.Hidden = False
Case Is = "2": Range("AUTH_3,AUTH_4,AUTH_5,AUTH_6").EntireRow.Hidden = True
Range("AUTH_1,AUTH_2").EntireRow.Hidden = False
Case Is = "3": Range("AUTH_4,AUTH_5,AUTH_6").EntireRow.Hidden = True
Range("AUTH_1,AUTH_2,AUTH_3").EntireRow.Hidden = False
Case Is = "4": Range("AUTH_5,AUTH_6").EntireRow.Hidden = True
Range("AUTH_1,AUTH_2,AUTH_3,AUTH_4").EntireRow.Hidden = False
Case Is = "5": Range("AUTH_6").EntireRow.Hidden = True
Range("AUTH_1,AUTH_2,AUTH_3,AUTH_4,AUTH_5").EntireRow.Hidden = False
Case Is = "6": Range("AUTH_1,AUTH_2,AUTH_3,AUTH_4,AUTH_5,AUTH_6").EntireRow.Hidden = False
End Select
End If
End Sub
The NUM_AUTH and AUTH_x will change based on the value lookup and rows i want to hide. This I need to happen 4 times on one sheet.
Thanks
Rusty