Collapse and Expand Rows in Excel

[BikeToWork]

I know this is the wrong forum for this post, but I posted the same question to the Bytes Excel forum and received no response. Also, I know there are a number of developers on this board who are experts in Excel and this is a continuation of my last "Sort Angst" post on this forum.

I have a spreadsheet of 75,000 rows that is sorted and grouped by AcctNum. There are several records for each AcctNum. The user wants to be able to collapse records so that only the first record for that AcctNum displays and the other records for that AcctNum are hidden. Then when the user clicks anywhere in that first AcctNum record, all of the other records for that AcctNum are displayed. Is it possible to do this? I have experience with Access and VBA, but I'm not sure how to get this done in Excel. Any advice is welcome.

[NeoPa]

Excel has an inbuilt feature that allows you to collapse and expand groups. However! This hides all the sub records and doesn't leave one showing. It allows you to show aggregated data for the group but no individual details.

If you feel this is acceptable then fine. It should be easy enough simply to tell the users how to do that using the standard interface.

Otherwise code will be required. Rows can be hidden and made visible either on a row-by-row basis or by a range of rows.

Let us know what you need to continue.

PS. I can't even see the questions in the Excel forum otherwise I'd look there myself.

[BikeToWork]

Thanks for the response, NeoPa. The user interface will not work because there are thousands of distinct AcctNum's and it would take forever to group them manually. How would I go about doing this with Excel VBA? I want the first row for an AcctNum to remain visible when the other rows with the same AcctNum are collapsed. Upon clicking the plus sign, all rows for the group are displayed. So basically, I want to group the second row for an AcctNum up to the last row for that AcctNum together so that the first row is still displayed when the group is collapsed. Any advice is welcome. The post on the Excel forum had over 100 views, but no responses.

https://bytes.com/topic/excel/answers/968560-expand-collapse-rows

[NeoPa]

I just had a view but I still can't see any question there. It's the same for all in that forum. I see all answers but never the first post.

NB. Expanding and collapsing by group is a single-step process. There's no need to handle each group individually.

OTOH what you're asking for is relatively straightforward in VBA anyway so let's see.

Assuming you have your AcctNum in column A and each Row has a vale set for AcctNum then the following code should work for you :

Option Explicit

Public Sub GroupCollapse()
    Dim lngRow As Long, lngTop As Long

    lngRow = 1
    Do Until Range("A" & lngRow) = ""
        lngTop = lngRow
        Do
            lngRow = lngRow + 1
        Loop While Range("A" & lngRow) = Range("A" & lngTop)
        If lngRow > lngTop + 1 Then _
            Rows(lngTop + 1 & ":" & lngRow - 1).Hidden = True
    Loop
End Sub

Public Sub GroupExpand()
    Rows.Hidden = False
End Sub

I assume you can handle triggering these routines as and when.

[BikeToWork]

Thanks, NeoPa. That worked a treat. I didn't realize this before but Excel puts a 1 and 2 at the top left of the spreadsheet where you can expand and collapse all rows by selecting the 1 or the 2. I only had to change a few lines in your code. The line:

Rows(lngTop + 1 & ":" & lngRow - 1).Hidden = True

I changed to Rows(lngTop + 1 & ":" & lngRow - 1).Group

Thanks again for the advice.

[NeoPa]

That sounds like what I was referring to in the first place. Did you try selecting the data then clicking on the Data ribbon and selecting Subtotal? That will handle much of the work for you without code. Unfortunately, it doesn't actually give you the first line but does allow you to select the 1, 2 or 3 to show the level of data required.

Personally, I believe my posted code most closely matches your original request but there are options there so choose the one you like the most.