根据你上传的图片,帮你做了一个代码,这个代码要放在当前工作表名称下的模块中
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a(1 To 3) '定义三个变量存放“销售、财务、采购”的位置
If Target.Column > 1 And Target.Column < 4 Then '在第二列或第三列选择时执行
x = [b65536].End(xlUp).Row
t = Target.Value
s1 = "销售部(点击合拢目录)"
s2 = "财务部(点击合拢目录)"
s3 = "采购部(点击合拢目录)"
s4 = "点击展开子目录"
For i = 7 To x
If Cells(i, 2) = s1 Then a(1) = i
If Cells(i, 2) = s2 Then a(2) = i
If Cells(i, 2) = s3 Then a(3) = i
Next
If t = s1 Then Rows(a(1) + 1 & ":" & a(2) - 1).EntireRow.Hidden = True '隐藏
If t = s2 Then Rows(a(2) + 1 & ":" & a(3) - 1).EntireRow.Hidden = True '隐藏
If t = s3 Then Rows(a(3) + 1 & ":" & x).EntireRow.Hidden = True '隐藏
If t = s4 And Cells(Target.Row, 2) = s1 Then Rows(a(1) + 1 & ":" & a(2) - 1).EntireRow.Hidden = False '显示
If t = s4 And Cells(Target.Row, 2) = s2 Then Rows(a(2) + 1 & ":" & a(3) - 1).EntireRow.Hidden = False '显示
If t = s4 And Cells(Target.Row, 2) = s3 Then Rows(a(3) + 1 & ":" & x * 2).EntireRow.Hidden = False '显示
End If
End Sub
已经调试过了,可直接使用,当增加或减少行数时,它能自己判断。