Hide Excel Rows With Outlining

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.

worksheet with all the outlines collapsed
worksheet with all the outlines collapsed

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.

Outline02

There are also buttons to expand and collapse all the sections at once.

Outline03

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.

Outline04

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.

Outline05

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.
________________________________

18 thoughts on “Hide Excel Rows With Outlining”

  1. 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

  2. @sam:
    I get how the macros give you progressive hide/unhide. How do you set the shortcuts to RightArrow, -LeftArrow? SendKeys?

  3. In Auto Open

    Application.OnKey “%{RIGHT}”, “ShowRowLevels”
    Application.OnKey “%{LEFT}”, “HideRowLevels”

    In Auto_Close

    Application.OnKey “%{RIGHT}”
    Application.OnKey “%{LEFT}”

  4. 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.

  5. 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’.

  6. […] 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 […]

  7. Excellent site and an excellent tutorial, I’m learning loads going through it all. Just wondering, how do you access the admin features?

  8. 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.

  9. Hi, Sam, can you give any thoughts on this outline rows:
    Fruits
    Orange
    Big Orange
    Big Navel Org
    Big Local Org
    Small Orange
    Small Navel Org
    Small Local Org
    Apple
    Big Apple
    Big Green App
    Big Red App
    Small Apple
    Small Green App
    Small Red App

  10. CK,
    Sorry I didn’t notice your comment in July!
    I’m guessing that the problem will be with named range scope – workbook vs worksheet names which have the same name. I’ll see if I can duplicate the problem.

  11. CK – I’m not sure what your problem is. To test this, I copied sheet1 and then operated the controls on both sheets. They work, and they work independantly. If you want to sent your file to me I’ll look at it. My email is in the sample file.

  12. thanks a lot AlexJ, i can’t open the sample file, basically i want to group them according to their indent levels:
    Fruits – indent 0
    Orange – indent2
    Big Orange – indent 4
    Big Navel Org – indent 6
    Big Local Org – indent 6
    Small Orange – indent 4
    Small Navel Org – indent 6
    Small Local Org – indent 6
    Apple – inde nt 2
    Big Apple – indent 4
    Big Green App – indent 6
    Big Red App – indent 6
    Small Apple – indent 4
    Small Green App – indent 6
    Small Red App – indent 6

  13. Hello,
    I am an excel Novice and need help with excel. I can send a screen shot (it didn’t let me paste it here) that can help me explain my problem. If someone can respond to me i can send them the screenshot and explain my question. i would appreciate our help.
    Kind regards,
    AK

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.