Excel Macro: Deleting All Shapes on Active Sheet

This Excel VBA macro deletes all the shapes on an active worksheet.

Macro Example

Sub DeleteShapes_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Delete
  Next shp

End Sub

Description

Variable Declaration

Sub...

  Dim shp As Shape

The 3rd line declares an object variable named shp that will refer to a Shape object.

Starts Looping

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes

The 5th line starts to loop through all the elements (Shape) of a collection (Worksheet.Shapes).

The code is “ActiveSheet.Shapes” but this means the Worksheet.Shapes object. I’ll explain about this.

[Application.]ActiveSheet

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

Because the ActiveSheet property is a member of the Excel.Global class, we can omit the Application property.

obj-Excel-Global-ActiveSheet

Worksheet.Shapes

The Worksheet.Shapes property returns a Shapes collection object that represents all the shapes on a specified worksheet.

obj-Excel-Worksheet-Shapes

So the code: ActiveSheet.Shapes means the Worksheet.Shapes object.

Deleting a Shape

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Delete

The 6th line deletes the shape by the Shape.Delete method.

obj-Excel-Shape-Delete

Loops Back

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Delete
  Next shp

The 7th line loops back to get the next Shape object.

After every shape is deleted, this macro ends.

Sub DeleteShapes_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    shp.Delete
  Next shp

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-macro-delete-all-shapes.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007