Hide/Unhide Rows Based on Cell Value

[nathandavies]

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

[Paul_Hossler]

1. Assuming that there's no rule for hiding, a long winded approach

2. The way this looks, CNumberPanels is a named range, and is not the cell that changed (i.e. Target)?




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Range("CNumberPanels").Value

Case 1
Rows("43:44").Hidden = False
Rows("45:95").Hidden = True
'rest of them

Case 12
Rows("44:55").Hidden = False
Rows("56:95").Hidden = True
End Select
End Sub

[nathandavies]

Thanks Paul, this seems to work better.