
2nd November 2015
Auto Row & Column Hiding
Automatically hide rows and columns throughout a workbook in an instant…
When managing a large number of reports, the process of hiding and un-hiding rows and columns can become laborious. So writing some code to do this automatically is the obvious step.
The first step is to create a row and a column within each report that denotes what to hide and what to show. In row 1, fill each cell with TRUE (show this cell’s column) or FALSE (hide this cell’s column). Do this until you have covered all the columns in use on this worksheet. Now assign a worksheet-specific name to this range of booleans – ‘ColumnHide’. Repeat the same process in column A to deal with all the used rows in the worksheet – give this the worksheet-specific name ‘RowHide’.
When automatically showing and hiding, there are two important elements that reduce the time it takes…
Firstly, to minimise the number of show/hide actions, these should only be performed once rather than incrementally through each row or column. We achieve this by using the VBA Union method to create a single range on which to act. For example:
For Each c In ws.Range("ColumnHide") If c.Value Then Set Colshowrange = Union(Colshowrange, c) Next c Colshowrange.EntireColumn.Hidden = False
Secondly, there can be some slowdown caused by Excel updating the pagebreaks as this process takes place. This could happen up to 4 times for a single worksheet (show columns, hide columns, show rows, hide rows), so we use the DisplayPageBreaks property to avoid this delay:
ws.DisplayPageBreaks = False
The real utility of the method we are designing is that by using the names RowHide and ColumnHide we are able to easily feed new ranges into the macro by having it cycle through all named ranges and then add any appropriate worksheets to a collection. Later on we can refer to the named ranges via the worksheet indices stored in this collection:
For Each Nm In ThisWorkbook.Names If Nm.Name Like "*!ColumnHide" Or Nm.Name Like "*!RowHide" Then On Error Resume Next collShowHide.Add Nm.Parent.Index, CStr(Nm.Parent.Index) On Error GoTo 0 End If Next Nm
As you may have spotted, by originally using cell values of TRUE or FALSE, we can also formulaically determine whether a row/column is shown/hidden. Perhaps some rows are only appropriate when viewing certain months, for instance. In this situation we would use a worksheet-change macro to call row & column hiding whenever a new month is selected.
Click here to download a working example where certain rows and columns are only visible if the Genus Compsognathus is selected on the worksheet. Macros must be enabled for this example to function. Below is the code in full:
Sub RowColHide() Dim c As Range Dim i As Integer Dim ws As Worksheet Dim Nm As Name Dim collShowHide As New Collection Dim Colhiderange As Range Dim Colshowrange As Range Dim Rowhiderange As Range Dim Rowshowrange As Range 'identify worksheets that need the row or column hiding treatment For Each Nm In ThisWorkbook.Names If Nm.Name Like "*!ColumnHide" Or Nm.Name Like "*!RowHide" Then On Error Resume Next collShowHide.Add Nm.Parent.Index, CStr(Nm.Parent.Index) On Error GoTo 0 End If Next Nm Application.Calculation = xlCalculationManual Application.ScreenUpdating = False 'cycle through worksheets that need the row or column hiding treatment For i = 1 To collShowHide.Count Set ws = ThisWorkbook.Sheets(collShowHide(i)) Application.StatusBar = "Auto-hiding... " & i 'preventing pagebreak updates while hiding takes place ws.DisplayPageBreaks = False 'clear the range variables before continuing Set Colhiderange = Nothing Set Colshowrange = Nothing Set Rowhiderange = Nothing Set Rowshowrange = Nothing 'create ranges of columns to hide and columns to show On Error GoTo Continue1 For Each c In ws.Range("ColumnHide") On Error GoTo 0 If c.Value Then If Colshowrange Is Nothing Then Set Colshowrange = c Else Set Colshowrange = Union(Colshowrange, c) End If Else If Colhiderange Is Nothing Then Set Colhiderange = c Else Set Colhiderange = Union(Colhiderange, c) End If End If Next c 'hide and show the appropriate columns - all at once via the created ranges Colshowrange.EntireColumn.Hidden = False Colhiderange.EntireColumn.Hidden = True GoTo Continue3 Continue1: Resume Continue3 Continue3: 'create ranges of rows to hide and rows to show On Error GoTo Continue2 For Each c In ws.Range("RowHide") On Error GoTo 0 If c.Value Then If Rowshowrange Is Nothing Then Set Rowshowrange = c Else Set Rowshowrange = Union(Rowshowrange, c) End If Else If Rowhiderange Is Nothing Then Set Rowhiderange = c Else Set Rowhiderange = Union(Rowhiderange, c) End If End If Next c 'hide and show the appropriate rows - all at once via the created ranges Rowshowrange.EntireRow.Hidden = False Rowhiderange.EntireRow.Hidden = True GoTo Continue4 Continue2: Resume Continue4 Continue4: On Error GoTo 0 ws.DisplayPageBreaks = True Next i Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.StatusBar = "Auto-hiding..." DoEvents Application.StatusBar = False End Sub