Help with codes

[eagle028]

I've started my excel with the following codes.

I've in mind (if possible) to add a set of codes such that if i click on say Row3, then Row 4-9 will appear whilst row 10 onwards remain hidden. Is this possible?



Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address 'first check which cell has been changed

Case "$D$8" 'if it's A1, run the A1 code
Select Case Target.Value
Case "Please select": Rows("10:34").EntireRow.Hidden = True
Case "1": Rows("9:14").EntireRow.Hidden = False
Rows("15:34").EntireRow.Hidden = True
Case "2": Rows("9:19").EntireRow.Hidden = False
Rows("20:34").EntireRow.Hidden = True
Case "3": Rows("9:24").EntireRow.Hidden = False
Rows("25:34").EntireRow.Hidden = True
Case "4": Rows("9:29").EntireRow.Hidden = False
Rows("30:34").EntireRow.Hidden = True
Case "5": Rows("9:34").EntireRow.Hidden = False
End Select

End sub

 

[VoG]

Try replacing that code with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Row
    Case 1: Rows("9:14").EntireRow.Hidden = False: Rows("15:34").EntireRow.Hidden = True
    Case 2: Rows("9:19").EntireRow.Hidden = False: Rows("20:34").EntireRow.Hidden = True
    Case 3: Rows("9:24").EntireRow.Hidden = False: Rows("25:34").EntireRow.Hidden = True
    Case 4: Rows("9:29").EntireRow.Hidden = False: Rows("30:34").EntireRow.Hidden = True
    Case 5: Rows("9:34").EntireRow.Hidden = False
End Select
End Sub

 

[eagle028]

I would like to keep the current codes but on top of that to have a code which enable me to do the following:

When I click on Row 3, Row 4-9 will appear whilst remaining rows remain hidden. I think it may be somewhat like a hyperlink function where when i click on the link, the Rows are unhidden.

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Row
    Case 1: Rows("9:14").Hidden = False: Rows("15:34").Hidden = True
    Case 2: Rows("9:19").Hidden = False: Rows("20:34").Hidden = True
    Case 3: Rows("4:9").Hidden = False: Rows("10:34").Hidden = True
    Case 4: Rows("9:29").Hidden = False: Rows("30:34").Hidden = True
    Case 5: Rows("9:34").Hidden = False
End Select
End Sub

 

[eagle028]

It works. Could you explain how does the code picks up that Row 3?

 

[VoG]

Yes, it is done using this:

Select Case Target.Row

Target is the cell that you clicked in, .Row gets the row number.