Delete all Shapes in Excel Workbook

In Excel a shape object can be:

A square box, round, call outs, star, arrow, line etc.,

Do you want to clear all these shapes from all worksheets? Then use this code.

Note: But just remember: Once the object is deleted & workbook is saved, the same shape cannot be recovered.

You only have to recreate these shapes dynamically again.

Sub Delete_All_Shapes_In_All_Sheets()
    'Declare Shape object & worksheet
    Dim shp As Shape, sh As Worksheet
    
    'Loop thru each worksheet
    For Each sh In ThisWorkbook.Sheets
    
        'Loop thru each shape in sheet
        For Each shp In sh.Shapes
        
            'Delete Shape object
            shp.Delete
        Next
    Next
    
End Sub

This code loops through each shape in the sheet & delete shapes, all of it, one by one, from the all worksheets.

If you have accidentally deleted any shape from the spreadsheet, then you will not be able to recover it again. You can only create a new one from Menu -> Insert -> Shapes or through dynamic shape creation.

What if we need to create only specific shape and not all?

Delete Shapes with Name

One idea to delete specific Shape is to use its name.

If not that, You can also choose other property of a shape like Title, Top, Width, height etc.,

Sub Delete_Shapes_with_Name(objNameToDelete As String)
    'Declare Shape object & worksheet
    Dim shp As Shape
    
    'Loop thru each shape in sheet
    For Each shp In ThisWorkbook.Sheets(1).Shapes
    
        'Verify that Shape has the name that needs to be deleted
        If shp.Name = objNameToDelete Then
            shp.Delete
        End If
        
    Next

End Sub

Inside code, check whether the property of any the shape matches with the value. If it matches then use .Delete on it.

Leave a Reply