Delete or Hide Shapes on a worksheet


Shapes collection

Members of the Shapes collection are for example:
1. Forms Controls from the Developer tab
2. Pictures, Shapes, charts, Insert Tab > Illustrations group

You see that all objects/controls are a member of the Shapes collection.
Below you find examples to delete or hide the members of this collection.
Tip: if you only want to hide all shapes for a moment then you can use the toggle shortcut Ctrl 6 (This shortcut is working for the whole workbook)

Manual Delete shapes
If you want to delete all objects/controls on a worksheet you can do it manual like this :
1. Press F5
2. Click on Special
3. Choose Objects
4. OK
5. Press the Delete button

Tip: You can do the same for Notes in the F5>Special dialog. (Not working on this moment on a Mac for Comments)

VBA code examples


Delete all shapes

Use this macro to delete all shapes on the worksheet, working in all Excel versions. The second macro you can use to Delete Notes and Comments on your worksheet.

Delete only specific shapes
What if you only want to delete Pictures or Forms controls.
You can loop through the collection and check the Type of the control.

12 = ActiveX control (control toolbox) or a linked or embedded OLE object. (Not exists in Mac Office)
13 = Picture
8 = Forms controls

For Type 8 we use the second macro in Excel for Windows to avoid the problem of losing Data Validation drop-downs on your worksheet. You not have to use the second macro on your Mac because we not have this problem in Mac Excel.
In the workaround macro above for Excel for Windows we use FormControlType = 2 in the loop (xlDropDown). Data Validation drop-downs do not have a TopLeftCell.Address and the macro will not delete this DropDowns.

Delete or Hide one shape

Because all objects/controls are a member of the shapes collection we can use this to delete or hide one button, picture or ?

Another way:
You can also point to the Forms control like ActiveSheet.Buttons, Instead of Buttons you can also use for example OptionButtons, CheckBoxes or DropDowns, see the examples below.

VBA code examples

If you want to know all the Type numbers of all Shapes on your worksheet you can run this macro to add a new worksheet with the names and Type numbers of all Shapes on your worksheet. You can find the number then that you must use in the code to Hide or delete the Shapes you want.
23/03/2024
Web design by Will Woodgate