472,977 Members | 1,601 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,977 software developers and data experts.

Expand and Collapse Rows

124 100+
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 6 '17 #1
1 5021
SioSio
272 256MB
Select "View Code" from the right-click menu of the tab of the sheet and write the following example code. This Example is hiding/show line range "3: 4"
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. Dim Cell_Adr As String
  3. Cell_Adr = Target.Address(RowAbsolute:=False, ColumnAbsolute:=False)
  4. If InStr(Target.Address, "2") Then
  5.     Rows("3:4").Hidden = True
  6. Else
  7.     Rows("3:4").Hidden = False
  8. End If
  9. End Sub
  10.  
Dec 24 '19 #2

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

Similar topics

1
by: Randy Starkey | last post by:
Hi, Is there a way to expand and collapse all if I have multiple implementations of this script on a single page? The script works well individually. Thanks! --Randy Starkey ---
1
by: Mary | last post by:
I am using dot net with C#, I have a working treeview, how do i do expand/collapse all? thank you
0
by: Shadow Lynx | last post by:
When using ASP.NET 2.0's built-in TreeView on a page with <BASE target = "AnythingBut_Self"></BASE> in the HEAD, the expand/collapse buttons fail to function. The reason for this is that the...
2
by: sam | last post by:
Hi all, I have a series of divs in 2 blocks say BLOCK1 and BLOCK2 and I want to use one click to expand/collapse all those in each block. But the code I came up with exapands all the divs in the...
0
by: jim | last post by:
Hi, I have a TreeView control that sits on the MasterPage. All of my other webpages inherit from that Master Page. The Treeview receives it's data using an XMLDataSource that has it's DataFile...
0
by: Tor Inge Rislaa | last post by:
I need to expand/collapse TreeView nodes to show/hide childnodes by clikcing on the node text (instead of using the expand/collapse icon). Does anyone know how to do that? (What property has to be...
0
by: Tor Inge Rislaa | last post by:
Controlling TreeView Expand/Collapse With a TreeView bound to an XML file as below I want to obtain the functionality where Chapter 2 and 3 are forced to collapse when Chapter 1 is...
0
by: antonyliu2002 | last post by:
I am interested in implementing expandable/collapsable rows like what's shown in the following snapshot, which is from american express ...
0
by: Homer J. Simpson | last post by:
A few weeks ago I asked for suggestions on how to persist a tree's node state to cookies, without causing postbacks on each click in the tree. I had a single .aspx file, with a row of buttons on...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.