By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Collapse and Expand Rows in Excel

100+
P: 124
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.
Apr 7 '17 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub GroupCollapse()
  4.     Dim lngRow As Long, lngTop As Long
  5.  
  6.     lngRow = 1
  7.     Do Until Range("A" & lngRow) = ""
  8.         lngTop = lngRow
  9.         Do
  10.             lngRow = lngRow + 1
  11.         Loop While Range("A" & lngRow) = Range("A" & lngTop)
  12.         If lngRow > lngTop + 1 Then _
  13.             Rows(lngTop + 1 & ":" & lngRow - 1).Hidden = True
  14.     Loop
  15. End Sub
  16.  
  17. Public Sub GroupExpand()
  18.     Rows.Hidden = False
  19. End Sub
I assume you can handle triggering these routines as and when.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
Apr 7 '17 #2

100+
P: 124
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/answer...-collapse-rows
Apr 7 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub GroupCollapse()
  4.     Dim lngRow As Long, lngTop As Long
  5.  
  6.     lngRow = 1
  7.     Do Until Range("A" & lngRow) = ""
  8.         lngTop = lngRow
  9.         Do
  10.             lngRow = lngRow + 1
  11.         Loop While Range("A" & lngRow) = Range("A" & lngTop)
  12.         If lngRow > lngTop + 1 Then _
  13.             Rows(lngTop + 1 & ":" & lngRow - 1).Hidden = True
  14.     Loop
  15. End Sub
  16.  
  17. Public Sub GroupExpand()
  18.     Rows.Hidden = False
  19. End Sub
I assume you can handle triggering these routines as and when.
Apr 7 '17 #4

100+
P: 124
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.
Apr 7 '17 #5

NeoPa
Expert Mod 15k+
P: 31,419
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.
Apr 8 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.