Efficiently hiding / unhiding many (+500) rows in Excel VBA

Question

Currently I am working on a Excel/VBA workbook that displays approx. 500 items in of a budget, each on a different row. I want the user to select the level of detail in the budget: on the highest level each individual item will be show, on the lower level of detail several items will be grouped under a single header.

The method outlined below (hiding/unhiding line-by-line) is very slow: it takes around 4 minutes to hide / unhide most of the items. I hope you have any hints for me how to speed up the programm!

When selecting a level of detail via a dialog box, the program runs through the following steps:

1) Updating screen is set off:

        Application.ScreenUpdating = False
        Application.EnableEvents = False

2) Per line item in the budget it is determined whether that row should be hidden or not. The code I am using for this is the following:

    with sheets("[name sheet here]")
         .Range("[identifier of budget line item here]").EntireRow.Hidden = False / True
         ...             
         [500 times]
         ...
    end with

3) Second, depending on level of detail, the text corresponding to a sub-header is made either white or black. This is done for around 20 rows:

    With Sheets("[name sheet here]").Range("[identifier of budget line item here]").Font
         .ThemeColor = xlThemeColorLight1
         .TintAndShade = 0
    End With

4) Lastly, again depending on level of detail, some lines are drawn again that become invisible when hiding some rows. This is done 10 times or so for various ranges:

    Range("[range here]").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
    End With

Solution

Just some general thoughts.

1. Apply an autofilter to the data range, with the user-supplied detail level used as the criterion for the filtering. If you don't want the result to look like an autofilter, you can programmatically hide the dropdown arrow at the top of each column. The method is described here.

2. If you don't want the user to be presented with an autofilter, you can still use it to do the filtering. The steps would be: use autofilter to filter out (not show) the rows you do not want to hide; assign the filtered rows to a range variable; remove the filter; hide in bulk the rows identified in the range variable. See this SO post for a working example of the technique.

3. Loop through the list using the Union function to gather all the rows you want to hide into a single range object and then hide the rows by setting the IsHidden property True for the rows in the range (HideRange.EntireRow.IsHidden = True). (See here for an example of the approach, which in that case is used to delete selected rows.)

Any of these techniques will take much less than 4 minutes to hide the rowsl.

Comments

[Richard Sanders]

Hi Chuff, tried the third approach and works like a charm: from 4 to 5 minutes to just a few seconds. Thanks for your help!

[Austin Wismer]

for some reason, this isn't working for me. I get the error: "Object doesn't support this property or method." What could I be doing wrong?

[Austin Wismer]

I'm able to select the end-product range to verify it is indeed the correct range. However, I'm still getting the above error. (FYI The range is not contiguous.) EDIT: It should be the "Hidden" property, not "IsHidden".