title: Efficiently hiding / unhiding many (+500) rows in Excel VBA
body: <p>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.</p>
  
  <p>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!</p>
  
  <p>When selecting a level of detail via a dialog box, the program runs through the following steps:</p>
  
  <p>1) Updating screen is set off:</p>
  
  <pre><code>        Application.ScreenUpdating = False
          Application.EnableEvents = False
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>    with sheets("[name sheet here]")
           .Range("[identifier of budget line item here]").EntireRow.Hidden = False / True
           ...             
           [500 times]
           ...
      end with
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>    With Sheets("[name sheet here]").Range("[identifier of budget line item here]").Font
           .ThemeColor = xlThemeColorLight1
           .TintAndShade = 0
      End With
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>    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
  </code></pre>