468,315 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,315 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 4046
SioSio
241 128KB
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
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.