nathandavies
04-25-2018, 07:27 AM
Hi All, i have created this code which hides/un-hides a number of rows based on a cell value. i have only completed 12 lines of code but i'm wondering if there is a better way to do this task as i have found that sometimes the code stops working and i also have 52 different runs of code to create.
the code works like this if CNumberPanels = Value then unhide that rows based on that value or alternatively hide rows is the value is lower.
The Row range is 44 to 95.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("CNumberPanels").Value = 12 Then
Rows("44:55").EntireRow.Hidden = False
Rows("56:95").EntireRow.Hidden = True
If Range("CNumberPanels").Value = 11 Then
Rows("44:54").EntireRow.Hidden = False
Rows("55:95").EntireRow.Hidden = True
If Range("CNumberPanels").Value = 10 Then
Rows("44:53").EntireRow.Hidden = False
Rows("54:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 9 Then
Rows("44:52").EntireRow.Hidden = False
Rows("53:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 8 Then
Rows("44:51").EntireRow.Hidden = False
Rows("52:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 7 Then
Rows("44:50").EntireRow.Hidden = False
Rows("51:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 6 Then
Rows("44:49").EntireRow.Hidden = False
Rows("50:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 5 Then
Rows("44:48").EntireRow.Hidden = False
Rows("49:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 4 Then
Rows("44:47").EntireRow.Hidden = False
Rows("48:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 3 Then
Rows("44:46").EntireRow.Hidden = False
Rows("47:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 2 Then
Rows("44:45").EntireRow.Hidden = False
Rows("46:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 1 Then
Rows("43:44").EntireRow.Hidden = False
Rows("45:95").EntireRow.Hidden = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
the code works like this if CNumberPanels = Value then unhide that rows based on that value or alternatively hide rows is the value is lower.
The Row range is 44 to 95.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("CNumberPanels").Value = 12 Then
Rows("44:55").EntireRow.Hidden = False
Rows("56:95").EntireRow.Hidden = True
If Range("CNumberPanels").Value = 11 Then
Rows("44:54").EntireRow.Hidden = False
Rows("55:95").EntireRow.Hidden = True
If Range("CNumberPanels").Value = 10 Then
Rows("44:53").EntireRow.Hidden = False
Rows("54:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 9 Then
Rows("44:52").EntireRow.Hidden = False
Rows("53:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 8 Then
Rows("44:51").EntireRow.Hidden = False
Rows("52:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 7 Then
Rows("44:50").EntireRow.Hidden = False
Rows("51:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 6 Then
Rows("44:49").EntireRow.Hidden = False
Rows("50:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 5 Then
Rows("44:48").EntireRow.Hidden = False
Rows("49:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 4 Then
Rows("44:47").EntireRow.Hidden = False
Rows("48:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 3 Then
Rows("44:46").EntireRow.Hidden = False
Rows("47:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 2 Then
Rows("44:45").EntireRow.Hidden = False
Rows("46:95").EntireRow.Hidden = True
Else
If Range("CNumberPanels").Value = 1 Then
Rows("43:44").EntireRow.Hidden = False
Rows("45:95").EntireRow.Hidden = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub