will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Hide Excel Rows With Outlining
Last week I described how I use X entries in hidden columns, so I can easily hide specific rows in an Excel worksheet. In the comments for that article, AlexJ mentioned that he uses outlining in his workbooks, to show and hide the rows and columns.
Alex sent me his sample file, and gave me the okay to share his technique with you. I don’t use outlining too often, and find it a bit fussy to work with, but Alex has put it to good use in his sample file. He’s made it easy for users to work with, by putting a simple set of buttons at the top of the worksheet. I’ll certainly give outlining another try, based on what Alex has done.
Buttons Let Users Manage the Worksheet
Here’s a screenshot of Alex’s worksheet with all the outlines collapsed. Only the section titles are showing.
In the frozen pane at the top, there are buttons that the user can click to show or hide a specific section of the worksheet. Click a button to expand a section, and you can see its detail rows.
There are also buttons to expand and collapse all the sections at once.
Set up the worksheet
Before the buttons will work, range names and outlines have to be added to the worksheet. Alex uses hidden columns to display the names, and only the workbook administrator would see those. Code creates an Admin toolbar when the worksheet is activated, with buttons to show specific sections, the Outline bars, and the row and column headings. When everything is set up, the Admin toolbar can be hidden.
Update -- Alex added this setup information:
You may notice that rows 2 and 3 are under the outline, but row 1 is not.
This is because Excel has an annoying habit of not unhiding the first row if it is under the outline AND the top rows are in a frozen pane.
As a result, my standard is to use row 1 as a visible spacer row, and rows 2, 3 (or more if required) are hidden as helper rows.
Sheet level names are assigned to the cells in the hidden column. Those names are used in the code that hides or shows the selected section.
Download the Sample File
You can download Alex’s sample file to hide Excel rows with outlining here. Or you can visit the Sample Excel Spreadsheets page at Contextures.com and in the UserForms, VBA, Add-Ins section, look for UF0008 - Hide Rows With Outlining.
________________________________
February 2nd, 2009 at 6:58 am
Wow, that's a nifty little interface Alex has designed.
February 2nd, 2009 at 12:22 pm
I have the below code to attached to two short cuts
Alt + Left Arrow - Expands Outlines
Alt + right Arrow - Contracts Outlines
Sub ShowRowLevels()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim Level As Long
Dim Row As Range
Level = 0
For Each Row In ActiveSheet.UsedRange.Rows.EntireRow
If Not Row.Hidden Then
If Row.OutlineLevel > Level Then
Level = Row.OutlineLevel
End If
End If
Next Row
ActiveSheet.Outline.ShowLevels RowLevels:=Level + 1
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sub HideRowLevels()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim Level As Long
Dim Row As Range
Level = 0
For Each Row In ActiveSheet.UsedRange.Rows.EntireRow
If Not Row.Hidden Then
If Row.OutlineLevel > Level Then
Level = Row.OutlineLevel
End If
End If
Next Row
ActiveSheet.Outline.ShowLevels RowLevels:=Level - 1
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
February 2nd, 2009 at 1:28 pm
@sam:
I get how the macros give you progressive hide/unhide. How do you set the shortcuts to RightArrow, -LeftArrow? SendKeys?
February 3rd, 2009 at 11:40 pm
In Auto Open
Application.OnKey "%{RIGHT}", "ShowRowLevels"
Application.OnKey "%{LEFT}", "HideRowLevels"
In Auto_Close
Application.OnKey "%{RIGHT}"
Application.OnKey "%{LEFT}"
February 4th, 2009 at 1:19 am
Thanks Sam, I've added your sample code to an outlined worksheet here: Hide Outline Rows With Keyboard Shortcut
February 5th, 2009 at 12:02 am
[...] Hide Excel Rows With Outlining [...]
February 6th, 2009 at 12:47 pm
Thanks for posting Alex's sheet, Debra.
Alex, cool code, thanks for sharing. I especially liked the "Toggle Headers" code - very useful in different applications.
February 6th, 2009 at 6:47 pm
Thanks Blayne, glad you like Alex's sample file.
February 6th, 2009 at 10:35 pm
One thing I forgot to discuss: which rows to hide for helper rows at the top of the sheet.
In the screen capture below the heading "Set up the worksheet", you may notice that rows 2 and 3 are under the outline, but row 1 is not.
This is because Excel has an annoying habit of not unhiding the first row if it is under the outline AND the top rows are in a frozen pane.
As a result, my standard is to use row 1 as a visible spacer row, and rows 2, 3 (or more if required) are hidden as helper rows.
Glad you liked the post, Blayne. Debra has been very kind to let me participate as a 'guest contributor'.
February 11th, 2009 at 12:01 am
[...] who recently shared his technique for hiding rows with Excel outlining, has created another useful sample. In this file, he lets users turn those data validation messages [...]
July 8th, 2010 at 2:29 am
Excellent site and an excellent tutorial, I'm learning loads going through it all. Just wondering, how do you access the admin features?
July 8th, 2010 at 4:04 pm
Hey Alex the outline buttons are very cool. Im new to VBA and am trying to apply the outline to more than one sheet in my workbook.
The buttons work perfect on one of my spreadsheets but the other ones are not functioning. I named the ranges on the other spreadsheets, but it keeps on giving me an error with the Me.range("rng.Toggle*) when I try to create a new command for new buttons.
If you could help me that would be great thanks.