Hide or show worksheets or workbooks
※ Download: Excel how to unhide all sheets
Dim PROFEXWorksheet As Worksheet 'A variable for going through all the worksheets For Each PROFEXWorksheet In ActiveWorkbook. You can also add the macros to your Personal Macro workbook to run them anytime you need. Hide or unhide rows and columns comfortably with Professor Excel Tools The Excel add-in provide a function for unhiding all hidden rows and columns on all sheets with one click.
This makes each sheet visible, which is the same as unhiding each sheet. You can also hide multiple sheets at once.
Unhiding Multiple Worksheets - If you have only a few hidden sheets and you seldom need to unhide them, it's just as easy to manually unhide them.
It's easy to hide worksheets in Excel, but unhiding multiple worksheets within a given workbook can be a tedious exercise. Users who don't know otherwise are relegated to unhiding worksheets one at a time. On the second page of this article I discuss using Excel's Custom Views feature to hide or unhide multiple sheets at once. There are a couple of different ways to hide worksheets in Excel. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide. Figure 1: Right-clicking on a worksheet tab provides a faster means for hiding worksheets. You can also hide multiple sheets at once. To do so, you'll first group the sheets. In any version of Excel, hold down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. You must repeat this action for each worksheet that you wish to unhide. You can save a couple of steps by right-clicking on a visible worksheet tab and choose Unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide. Figure 2: Excel's Unhide Sheet command only allows you to unhide one worksheet at a time. Fortunately, the tedium of unhiding worksheets one at a time can be eliminated by use of Excel's Custom Views feature. Think of Custom Views as snapshots of workbook settings — such as the hidden or visible status of individual worksheets — that you can toggle at will. Figure 3: Create a baseline view that shows all worksheets before you hide any worksheets. Going forward, you can toggle the view by selecting the Custom Views command and then double-clicking the view of your choice, or click once on the view and then click Show as indicated in Figure 4. This article only scratches the surface of what's possible with Custom Views. I'll explore this feature further in future articles. Figure 4: A custom view allows you to unhide multiple worksheets with just a couple of mouse clicks. See all articles by. About the author: David H. Ringstrom, CPA heads up Accounting Advisors, Inc. Contact David at or follow him on. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets is a different story, however. Excel only allows you to unhide one at a time. If you have many worksheets you want to unhide, this can be very tedious. The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook: Sub UnhideAllSheets Dim wsSheet As Worksheet For Each wsSheet In ActiveWorkbook. Thanks for your help and for posting this article! I too had to read the comments to see the value in reading the second page. Please consider telling the reader the information they are looking for is on the second page... Thank you for this article. In your case the solution would be to use Visual Basic for Applications to create a dialog box that shows you all of the sheets in the workbook and allows you to hide or unhide based on making selections from the list. Creating multiple custom views would probably be easier unless for your purposes the mix of sheets is constantly changing. Unhide all hidden worksheets by VBA code this is what I was personally hoping to find in this article, but the custom views could come in handy as well The following short VBA code also can help you display all of the hidden sheets at the same time. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window. Sub UnhideAllSheets Dim ws As Worksheet For Each ws In ActiveWorkbook. Press the F5 key to run this macro. And the hidden sheets will be displayed at once. Firstly i would like to say thanks, because your article gives such a good information how to hide or unhide worksheet in a workbook. Thank you for sharing a good information.
After unhiding three worksheets like this, you will start feeling annoyed. The answer to this question like many other Excel questions… is: Keyboard-Shortcuts. How can I do that. Alternatively, you can also hide multiple sheets at once by holding down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. The Unhide menu only allows you to select one sheet at a time. Thinking maybe with a Custom View where Excel would know to ask only one time. If you need assistance with adding the VBA module, please refer to. Jeff CJV - December 31, 2017 Jon, my hidden worksheets are hidden for security, not just appearance.
