vba to hide rows based on cell value in specific cell

[mychi11]

Hi all,
I need to vba code to hide the row based on value in one cell. To illustrate, when the cell A29=1. I would like the row 55 to 103 to hide. When A29=2, I would like row 56 to 103 to hide so on and so forth till 50. The following code works for the first two, but as I add more code , the code fails to work. Wonder if anybody can help. Many thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

1. If Range("A29").Value = 1 Then
2. Rows("55:103").EntireRow.Hidden = True
3. Else
4. Rows("55:103").EntireRow.Hidden = False
5. End If
7. If Range("A29").Value = 2 Then
8. Rows("56:103").EntireRow.Hidden = True
9. Else
10. Rows("56:103").EntireRow.Hidden = False
11. End If
13. End Sub

 

[WhoCanDo]

Not sure of the reason but this works ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
x = Range("A29").Value

Select Case x
Case 1: Rows("55:103").EntireRow.Hidden = False
Rows("55:103").EntireRow.Hidden = True
Case 2: Rows("55:103").EntireRow.Hidden = False
Rows("56:103").EntireRow.Hidden = True
Case Else
Rows("55:103").EntireRow.Hidden = False
End Select

End Sub

 

[Michael M]

Are you sure you want a SelectionChange event....every time you change cells the code will run ??
I would have thought a straight ChangeEvent would be better

Private Sub Worksheet_Change(ByVal Target As Range)
Rows("55:103").EntireRow.Hidden = False
x = Range("A29").Value
Select Case x
Case 1: Rows("55:103").EntireRow.Hidden = True
Case 2: Rows("56:103").EntireRow.Hidden = True
End Select

End Sub

 

[mychi11]

thank you so much. This works like charm

 

[Michael M]

Glad WE could help

 

[mychi11]

Glad WE could help

thank you both of you. I was wondering if i can add another few lines below the line
say if B3 equals to "APPLE", i would like row 2:5 and row 6:10 to hide.
B3 equals to "ORANGE", i would like 8:11 and 9:50 to hide