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

Expand and Collapse Rows

P: 124
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
Share this Question
Share on Google+
1 Reply

P: 150
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
Dec 24 '19 #2

Post your reply

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