title: Excel VBA: Hide all columns, then unhide some columns
body: <p>I hesitate to ask this because I do have a workaround, but I'd prefer a cleaner answer. </p>
  
  <p>I am using Excel 2010 and I have a procedure that does some basic formatting on a new sheet: hides all columns, sets the header row text, formats the header row, unhides the columns the header row uses. The problem is that the unhide doesn't quite work. After the procedure is run, the worksheet looks like all of the columns are still hidden, but if I resize the formula bar, the columns that the procedure unhid appear as I would expect. Even when the workbook is saved, closed, and reopened the columns don't appear until I resize the formula bar.</p>
  
  <p>I tried using <code>DoEvents</code> to refresh the screen. I tried setting <code>Application.ScreenUpdating</code> to true even though I was never setting it to false. I even tried hiding and unhiding the formula bar through VBA. The only thing that works (my workaround) is to resize the formula bar as part of the procedure. It does work, but seems like it shouldn't be necessary. It might work to activate the range before I unhide it, but I prefer not to use <code>Activate</code> or <code>Select</code> in VBA.</p>
  
  <p>Any thoughts?</p>
  
  <pre><code>Private Sub FormatSheet(sh As Worksheet)
      Dim HeaderText As Variant
      Dim EndCol As Long
      Dim Header As Range
  
      'header items for sheet
      HeaderText = Array("DATE", "USER", "BC", "TC", "SUM")
  
      'get last column index based on headers
      EndCol = UBound(HeaderText) - LBound(HeaderText) + 1
  
      With sh
          'hide all columns in the sheet
          .Columns.Hidden = True
  
          'set the header range
          Set Header = .Range(.Cells(2, 1), .Cells(2, EndCol))
  
          'set the header text
          Header = HeaderText
  
          'set the header row formatting
          With .Rows(2)
              .Font.Bold = True
              .Interior.Color = RGB(217, 217, 217)
              With .Borders(xlEdgeBottom)
                  .LineStyle = xlContinuous
                  .Weight = xlThin
              End With
          End With
  
          'unhide the columns used by the header
          Header.EntireColumn.Hidden = False
  
          'resize the formula bar to force the unhide to work
          Application.FormulaBarHeight = 5
          Application.FormulaBarHeight = 1
  
          'autofit columns
          .Columns.AutoFit
      End With
  End Sub
  </code></pre>