I’m sure most of us have experienced frustration at Excel’s refusal to allow us to unhide multiple sheets at a time.
This is no April Fools' joke! I know it seems illogical to allow the hiding of multiple sheets but not the un-hiding.
Ho, hum....here are a couple of easy fixes:
1. Custom Views
A custom view is a snapshot of how the workbook looks, in terms of structure not content, at a point in time.
So, all you need to do is create a custom view of your workbook with all of the sheets unhidden, then you can merrily hide the sheets in the knowledge that you can quickly unhide them again by selecting your 'custom' view of the workbook.
To create a Custom View first make sure all of your sheets are unhidden > on the View tab select Custom Views > click ‘Add’ in the dialog box:
In the ‘Add View’ dialog box give your Custom View a name:
Tip: if you keep the ‘Print Settings’ and ‘Hidden rows, columns and filter settings’ checked then when you ‘Show’ your Custom View Excel will unhide any hidden rows/column and filters will be removed.
Ok, now you’ve set up your Custom View you can quickly unhide your sheets by selecting it:
View tab > Custom Views > double click the view you want or click the ‘Show’ button:
Note: Custom Views are specific to each Workbook. i.e. If you want to be able to easily unhide multiple sheets in other workbooks you will need to set up a Custom View in each workbook.
If this annoys you too then maybe the next option is for you.
2. VBA to Unhide Multiple Sheets
Now, Custom Views are fine if you already have your sheets unhidden, but what if you have inherited a workbook with 100 hidden sheets? What?....100 hidden sheets is possible!
Well, you still have to unhide them before you can set up your Custom View.
So, here is some VBA code that will unhide all sheets quickity quick:
Option Explicit Sub UnhideAllSheets() Dim HiddenSheet As Worksheet For Each HiddenSheet In ActiveWorkbook.Worksheets HiddenSheet.Visible = xlSheetVisible Next HiddenSheet End Sub
Go ahead and paste it into your Personal Macro Workbook so it’s there for you to use whenever you need.
If you don't have a Personal Macro Workbook, here's how to set it up.
To add the VBA code to your Personal Macro Workbook:
- Alt+F11 to open the VB Editor, or click on the Developer Tab and click on the Visual Basic icon > locate the ‘PERSONAL.XLSB’ project > right click and Insert a Module (if you don’t already have one):
- Then paste in the code from above in the right hand side of the VB editor:
Add an Icon to your Quick Access Toolbar
Since you’re on a roll why not set up an icon on the Quick Access Toolbar (QAT) so it’s always to hand?
To do this right click the QAT > select ‘Customize the Quick Access Toolbar’:
- Choose ‘Macros’ in the ‘Choose commands from' list
- Double click the Macro to add it to your QAT or click the 'Add' button:
Now Make it Funky
You can even give your shortcut a funky icon so you can easily identify it.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.