470,575 Members | 1,867 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

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 4237
SioSio
269 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

Post your reply

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

Similar topics

1 post views Thread by Randy Starkey | last post: by
1 post views Thread by Mary | last post: by
reply views Thread by Tor Inge Rislaa | last post: by
reply views Thread by Tor Inge Rislaa | last post: by
reply views Thread by antonyliu2002 | last post: by
reply views Thread by Homer J. Simpson | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.