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.

Buttons at Top

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 from the Sample Excel Spreadsheets page at Contextures.com. In the UserForms, VBA, Add-Ins section, look for UF0008 – Hide Rows With Outlining.

Also, thanks to Sam, who shared his code in the comments below. You can download a file with Sam’s code at this link: UF0009 – Hide Outline Rows With Keyboard Shortcut. Sam’s code uses the OnKey method, so the left and right arrows run macros that show and hide the outline.
________________________________