How to Expand and Collapse Cells without Pivoting or Using Group Data Options

A few years ago I wrote something like this for a customer. The code below demonstrates the macro attached to a button that expands/collapses a pre-defined Named-Range ("pRngPurchaseOrders").

This range has "guard rows" which are just empty rows at the beginning and end of the range, and thus if there are <= 2 rows in the range, we assume that it is empty and call an update routine to get data from a database to fill-in the range (if you don't need this, just ignore that branch and treat the range as collapsed).

' 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

To use this I simply created a button on the sheet with a "+" in the text next to the named range, and assigned this macro to it.