This post will guide you how to hide all objects in your worksheet in Excel. How do I show all hidden objects in your current worksheets with VBA Macro in Excel.
If you want to hide all objects in your current worksheet, such as: pictures, comments, charts, Textbox, etc. You can use Selection pane feature or write down an Excel VBA Macro to show or hide all objects. Let’s see the following introduction.
Table of Contents
If you want to hide or unhide all objects in the current worksheet, you can use the Selection pane feature to achieve the result. Just do the following steps:
Step1: go to HOME tab, click Find & Select command under Editing group. And select Selection Pane … menu from the drop down menu list. And the Selection pane will be shown.
Step2: you should see that all of the objects are listed in the Selection pane. And You can click Hide All button to hide all objects. Or you can click Show All button to show all hidden objects.
Step3: if you only want to hide or show specific objects in Excel, you just need to click its eye button, or click eye button again to show it.
You can also use an Excel VBA macro to achieve the same result of hiding or showing all objects in the current worksheet. Here are the steps:
Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step2: then the “Visual Basic Editor” window will appear.
Step3: click “Insert” ->”Module” to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “Save” button.
Sub HideAllObjects() Dim ob As Shape For Each ob In ActiveSheet.Shapes ob.Visible = False Next End Sub
Step5: back to the current worksheet, then run the above excel macro. Click Run button.
All objects in the current worksheet would be hidden.
If you want to show all hidden objects with VBA code, you can use the following VBA Macro:
Sub ShowAllObjects() Dim ob As Shape For Each ob In ActiveSheet.Shapes ob.Visible = True Next End Sub
3. Video: How to Hide or Show Objects in Excel
In this video, you will learn how to hide or show objects in Excel using the selection pane feature as well as VBA code.