Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable. I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide. I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.
I consider the following, essential in a framework for hiding rows and columns:
- Inserting rows and columns in later should not leave you adjusting your code all over again. Here is where the recording and playing method fails miserably. Having a couple of arrays with row and column numbers is also not immune to inserting new rows and columns.
- In addition to hiding cells, you should also allow for un-hiding essential rows and columns that were inadvertently hidden.
- Another consideration is that, you might have to conditionally hide or show some cells in your dashboards
- Finally, it should also be able to hide unused columns to the right, and rows below the used cells
These considerations point to a system that uses markers right on the sheet and not explicitly specified in code. So I decided to go with Boolean markers on the first row and column of every sheet. The entire row or column is hidden only if the value is set to True, otherwise, it is un-hidden.
Download HideRowsColumns.xlsm from Dropbox to give it a spin. I have also added in a couple of cover macros you can use in your projects with minor tweaks. You definitely have to un-hide all the cells in a sheet, to prepare your sheets for development. I have added in a sub for that as well. You may also explicitly specify the ranges you have stored the Boolean markers in – Check the ‘HideConditional’ Sub in ‘modCovers’. You can even loop through a bunch of sheets to prepare your entire workbook for the show!
Read on, if you are interested in understanding how the code works:
'###################################################################
'PrepareSheet: Macro to Hide or Un-Hide Rows and Columns in a sheet
'and to prepare it for viewing.
'###################################################################
'Arguments:
'WhichSheet - Which Worksheet Object to work on
'HideRowsRange - The Range that has Boolean Markers to hide
'rows
'HideColumnRange - The Range that has Boolean Markers to hide
'Columns
'HideUnusedCells - Boolean variable to specify if the user would
'like to unhide all the unused cells to the right and bottom
'HideGridLines - Boolean variable to specify if the user would
'like to Show/Hide Gridlines
'HideHeadings - Boolean variable to specify if the user would
'like to Show/Hide Row and Column Headings
'HideFormulaBar - Boolean variable to specify if the user would
'like to Show/Hide the FormulaBar
'###################################################################
'Author : Ejaz Ahmed
'Date : 25 April 2014
'Email : StrugglingToExcel@outlook.com
'Website : http://strugglingtoexcel.wordpress.com/
'###################################################################
Sub PrepareSheet(Optional ByRef WhichSheet As Worksheet, _
Optional ByRef HideRowsRange As Range, _
Optional ByRef HideColumnsRange As Range, _
Optional ByVal HideUnusedCells As Boolean = True, _
Optional ByVal HideGridLines As Boolean = True, _
Optional ByVal HideHeadings As Boolean = True, _
Optional ByVal HideFormulaBar As Boolean = False)
Dim rngEach As Range
Dim rngTemp As Range
'Work on the Active sheet if not specified
If WhichSheet Is Nothing Then
Set WhichSheet = Application.ActiveSheet
End If
WhichSheet.Activate
'Use the first Column of the UsedRange, if not specified
If HideRowsRange Is Nothing Then
Set HideRowsRange = WhichSheet. _
UsedRange.Columns(1)
End If
'Use the first Row of the UsedRange, if not specified
If HideColumnsRange Is Nothing Then
Set HideColumnsRange = WhichSheet. _
UsedRange.Rows(1)
End If
'Hide the Unused cells if the user chose to
If HideUnusedCells Then
'Unhide everyhting first (to get the right UsedRange)
WhichSheet.Cells.EntireColumn.Hidden = False
WhichSheet.Cells.EntireRow.Hidden = False
'Get the Used Range
Set rngTemp = WhichSheet.UsedRange
'Get the last Row in the UsedRange
Set rngTemp = rngTemp.Rows(rngTemp.Rows.Count)
'Get the Next Row in the Sheet
Set rngTemp = WhichSheet.Rows(rngTemp.Row + 1)
'Get all the rows below it
Set rngTemp = Range(rngTemp, rngTemp.End(xlDown))
'Hide those Rows
rngTemp.EntireRow.Hidden = True
'Do the same for the Columns
Set rngTemp = WhichSheet.UsedRange
Set rngTemp = rngTemp.Columns(rngTemp.Columns.Count)
Set rngTemp = WhichSheet.Columns(rngTemp.Column + 1)
Set rngTemp = Range(rngTemp, rngTemp.End(xlToRight))
rngTemp.EntireColumn.Hidden = True
End If
'I could have achieved the same effect with the following
'commented out code, but the code written above is just
'way faster.
'WhichSheet.Cells.EntireColumn.Hidden = True
'WhichSheet.Cells.EntireRow.Hidden = True
'WhichSheet.UsedRange.EntireColumn.Hidden = False
'WhichSheet.UsedRange.EntireRow.Hidden = False
'Loop through HideRowsRange
For Each rngEach In HideRowsRange.Cells
If rngEach.Value = True Then
'Hide the Row if the cell's value is set to True
rngEach.EntireRow.Hidden = True
Else
'Unhide the row otherwise
rngEach.EntireRow.Hidden = False
End If
Next rngEach
'Do the same thing for the Columns
For Each rngEach In HideColumnsRange.Cells
If rngEach.Value = True Then
rngEach.EntireColumn.Hidden = True
Else
rngEach.EntireColumn.Hidden = False
End If
Next rngEach
'Hide/Show Gridlines, Headings and FormulaBar
ActiveWindow.DisplayGridlines = Not HideGridLines
ActiveWindow.DisplayHeadings = Not HideHeadings
Application.DisplayFormulaBar = Not HideFormulaBar
'Scrollback up after all the hiding
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End Sub
Nice code. I think that I will use it even for sheets without dummy rows / columns as the hide non used, grid lines and headings is exactly what I would like to present in “boss mode”. I think it can even used the macro in the activate sheet event to prevent any user to enter on edit mode by mistake.
I totally agree, you can use this even if you don’t have to hide Rows/Columns. That is why I coded it such that only a “True” value will hide Rows/Columns.
Glad you like it.
However, there is the issue with Excel where it does not update used ranges unless you reopen the file. So it is safe to put down a couple of True’s to the right and bottom of your used range.
Hi there. I like the idea. However, I am always trying to avoid ways of looping through cells/ranges it can really kill the performance of a sheet. With the TRUE/FALSE distinction there is no clever way to select a range quickly.
Another idea is to generate errors on the rows/columns you want to hide e.g. =IF(B2<>””, NA(), TRUE) . Then for example use
Range(“A:A”).SpecialCells(xlCellTypeFormulas, 16).entirerow.hidden = True
This avoids looping through cells/ranges and is a lot quicker for conditional hiding/showing.
Find this specialCells extremely useful for avoiding looping through ranges/cells in certain circumstances.
Hi Luke,
Thank you for the suggestion and following my blog. I am afraid I have to disagree with you on part of your comment.
Using a for each loop is very inefficient I agree. Because, it has to initialize a range object each at each iteration.
Having a single range object, and looping through it using row and column numbers is not inefficient at all.
In fact, even the special cells method has to perform a loop implicitly.
Let me know what you think.
Regards,
Ejaz
Thanks Ejaz,
I love your blog, full of really great ideas and expert code. I just thought this was one area I could contribute and add a little improvement. Sorry just love my vba as well, as it is only language can use at work (computers are severely locked down, so can’t really distribute an exe or a dll).
The loop at:
For Each rngEach In HideRowsRange.Cells
is a performance killer!
Agree that specialCells has some kind of internal implicit loop, however this is done natively on excel side, with only one call from vba to the worksheet. Every time the loop calls a range, it is crossing from vba to the excel worksheet side, and this is what hurts performance.
You just cannot compete with specialCells performance from vba code (although range = variant, then loop through variant might come close).
You can also use this with other enumerations of specialCells e.g.
range.specialCells(xlCellTypeVisible).entirerow.hidden = false
or
range.specialCells(xlCellTypeBlanks).entirerow.hidden = true
Did a quick test over 1000 iterations for 1000 rows with every second row marked to be hidden (with TRUE or NA())
– loop over range = 147.05 seconds for 1000 iterations
– specialCells method = 1.14 seconds for 1000 iterations
Thanks – once again, your blog is brilliant, but just wanted to contribute and thought this was an area the code could be more efficient.
Thanks Ejaz,
Luke
I have an optimized version, that I have been using for a while now. Did not realize I did not update my blog. Thank you for the suggestion. I will update my code.
I’d be grateful, if you would give me some feedback on my other post as well.
Very helpful from both of you. Just what I needed. Cheers