如何在不进行数据透视或使用组数据选项的情况下展开和折叠单元格

Question

如果没有透视表或组数据，如何在excel中添加展开和折叠按钮，如附图所示？

展开和折叠按钮

Answer 1

几年前，我为一位客户写了这样的文章。下面的代码演示了附加到按钮的宏，该按钮可展开/折叠预定义的命名范围("pRngPurchaseOrders"(。

该范围具有"；"保护行"；它们只是在范围的开始和结束处的空行，并且因此如果存在&lt范围中的2行，我们假设它是空的，并调用更新例程从数据库中获取数据以填充该范围(如果不需要，只需忽略该分支并将该范围视为折叠(。

' Expand or collapse the PurchaseOrder display region
Sub ExpandPurchaseOrders()
Debug.Print Format(Now, "HH:mm:SS  "), "ExpandPurchaseOrders begin"
' get the PurchaseOrders range
Dim nPurchaseOrders As Name, rPurchaseOrders As Range, lenPurchaseOrders As Long
Set nPurchaseOrders = ws.Names("pRngPurchaseOrders")
Set rPurchaseOrders = nPurchaseOrders.RefersToRange
lenPurchaseOrders = rPurchaseOrders.Rows.Count

' if len=2 then assume we are expanding/expanded and updatePurchaseOrders
' if len > 2 and row(2) is invisible then we are collapsed, so expand and then updatePurchaseOrders
' if len > 2 and row(2) is visible then collapse

If lenPurchaseOrders = 2 Then
' no rows, so we are expanding, but effectively already expanded
PurchaseOrdersUpdate

ElseIf lenPurchaseOrders > 2 Then
Dim i As Long
If rPurchaseOrders.Rows(2).Hidden = True Then
'EXPAND: unhide all of the PurchaseOrder rows
For i = 2 To lenPurchaseOrders - 1
'first and last rows are hidden and used as anchors, so skip them
rPurchaseOrders.Rows(i).Hidden = False
Next i
' and update
PurchaseOrdersUpdate

Else
'COLLAPSE: hide all of the PurchaseOrder rows
For i = 2 To lenPurchaseOrders - 1
'first and last rows are hidden and used as anchors, so skip them
rPurchaseOrders.Rows(i).Hidden = True
Next i

End If

End If

Debug.Print Format(Now, "HH:mm:SS  "), "ExpandPurchaseOrders Done"
End Sub

为了使用它，我只需在工作表上创建一个按钮，在命名范围旁边的文本中使用"+"，并将该宏分配给它。