How to Hide or Show Objects in Excel

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.

Hide or Show All Objects


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:

#1 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.

hide show objects1

#2 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.

hide show objects2

hide show objects3

#3 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.

hide show objects4

Hide or Show All Objects with VBA


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:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

hide show objects5

Sub HideAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = False
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

hide show objects6

 

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

 

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar