Macro 36: Unhide All Rows and Columns
When you are auditing a spreadsheet that you did not create, you often want to ensure you’re get-
ting a full view of what is exactly in the spreadsheet. To do so, you need to ensure that no columns
and rows are hidden. This simple macro automatically unhides all rows and columns for you.
How it works
In this macro, we call on the Columns collection and the Rows collection of the worksheet. Each
collection has properties that dictate where their objects are hidden or visible. Running this macro
unhides every column in the Columns collection and every row in the Rows collection.
Sub Macro36()
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
End Sub
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In
drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
