excel 双击隐藏行

yfwxisqw 于 2023-01-18 发布在其他

关注(0)|答案(2)|浏览(154)

下面是我发现的一个在Excel中双击隐藏/打开完整行的示例。
它只适用于几行，但如果我想这样做100行，这是一个可怕的工作。
有没有可能使它更友好的代码？

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

excel

来源：https://stackoverflow.com/questions/75134882/hide-rows-with-double-click

2条答案

按热度按时间

ogq8wdun1#

试试这个：

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

赞(0）回复(0）举报 2023-01-18

yhxst69z2#

您可以使用此代码

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

@foxfires评论后更新：如果您喜欢展开/折叠的想法，可以使用以下代码：

Public Sub groupRows(ws As Worksheet)

Dim c As Range
Set c = ws.Cells(9, 1)

While LenB(c.Text) > 0
    c.Offset(1).Resize(6).EntireRow.Group
    Set c = c.Offset(7)
Wend

With ws.Outline
    .SummaryRow = xlSummaryAbove
    .ShowLevels 1
End With

End Sub

赞(0）回复(0）举报 2023-01-18

我来回答

excel 双击隐藏行

2条答案

相关问题

热门标签

最新问答