hide rows with double click-CodePudding

Below a example I found to hide/open complete rows in Excel with a doubleclick.

It works for a few lines but if I want to do this for 100 lines it's a terrible job.

Is it possible to make this more code-friendly?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0, 0) = "A9" Then
    Cancel = True
    Rows("10:15").Hidden = Not Rows("10:15").Hidden
End If
If Target.Address(0, 0) = "A16" Then
    Cancel = True
    Rows("17:22").Hidden = Not Rows("17:22").Hidden
End If
If Target.Address(0, 0) = "A23" Then
    Cancel = True
    Rows("24:29").Hidden = Not Rows("24:29").Hidden
End If
If Target.Address(0, 0) = "A30" Then
    Cancel = True
    Rows("31:36").Hidden = Not Rows("31:36").Hidden
End If
If Target.Address(0, 0) = "A37" Then
    Cancel = True
    Rows("38:43").Hidden = Not Rows("38:43").Hidden
End If
If Target.Address(0, 0) = "A44" Then
    Cancel = True
    Rows("45:50").Hidden = Not Rows("45:50").Hidden
End If

CodePudding user response：

You can use this code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row < 9 then Exit Sub
If (Target.Row - 2) Mod 7 = 0 Then  'e.g. 9, 16, 23, 30
    hideRows Target.Row   1
End If
End Sub


Private Sub hideRows(startRow As Long)

With Me.Rows(startRow).Resize(6)
    .Hidden = Not .Hidden
End With

End Sub

CodePudding user response：

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub
    Dim r As Long
    r = Target.Row
    If (r - 2) Mod 7 = 0 And r > 2 Then
        Rows(r   1).Resize(6).Hidden = Not (Rows(r   1).Resize(6).Hidden)
        Cancel = True
    End If
End Sub