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.

Sub Shapes1()
'Delete all Objects except Notes and Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

Sub NotesComments()
'This will delete all Notes and Comments in Mac Excel
ActiveSheet.Cells.ClearComments
End Sub

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.

Sub Shapes2()
'Loop through the Shapes collection and use the Type number of the control
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes

' 8 = Forms controls
If myshape.Type = 8 Then myshape.Delete
' You can also use myshape.Visible = False

Next myshape
End Sub

Sub Shapes3()
'Dave Peterson and Bob Phillips
'Example only for the Forms controls type 8 in Windows
'To avoid losing data validation dropdowns in Excel for Windows
Dim shp As Shape
Dim testStr As String

For Each shp In ActiveSheet.Shapes

If shp.Type = 8 Then
If shp.FormControlType = 2 Then
testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0
If testStr <> "" Then shp.Delete
Else
shp.Delete
End If
End If

Next shp
End Sub
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 ?

Sub Delete_One_Shape()
ActiveSheet.Shapes("YourShapeName").Delete
End Sub

Sub Hide_One_Shape()
ActiveSheet.Shapes("YourShapeName").Visible = False
End Sub

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.

Sub Forms1()
'Delete All Forms buttons
ActiveSheet.Buttons.Delete
End Sub

Sub Forms2()
'Hide All Forms buttons
ActiveSheet.Buttons.Visible = False
End Sub

Sub Forms3()
'Delete one Forms button
ActiveSheet.Buttons("Button 1").Delete
End Sub

Sub Forms4()
'Hide one Forms button
ActiveSheet.Buttons("Button 1").Visible = False
End Sub

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.

Sub ListAllShapesActiveSheet()
Dim NewSheet As Worksheet
Dim MySheet As Worksheet
Dim myshape As Shape
Dim I As Long

Set MySheet = ActiveSheet
Set NewSheet = Worksheets.Add

With NewSheet
.Range("A1").Value = "Name"
.Range("B1").Value = "Visible(-1) or Not Visible(0)"
.Range("C1").Value = "Shape type"
I = 2

For Each myshape In MySheet.Shapes
.Cells(I, 1).Value = myshape.Name
.Cells(I, 2).Value = myshape.Visible
.Cells(I, 3).Value = myshape.Type
I = I + 1
Next myshape

.Range("A1:C1").Font.Bold = True
.Columns.AutoFit
.Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _
Order1:=xlAscending, Header:=xlYes
End With

End Sub
25-March-2021
Web design by Will Woodgate