Excel Macro: Deleting All Shapes on Active Sheet without Looping

I’ve described the Excel VBA macro that deletes all the shapes on an active worksheet.
Actually, we can do the same thing without looping.

Macro Example

Sub DeleteShapes_ActiveSheet()

 ActiveSheet.Shapes.SelectAll
 Selection.Delete

End Sub

Description

Selecting All Shapes

Sub...

 ActiveSheet.Shapes.SelectAll

The 3rd line selects all the shapes on an active sheet.

[Application.]ActiveSheet

 ActiveSheet...

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

obj-Excel-Application-ActiveSheet

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

obj-Excel-Global-ActiveSheet

Worksheet.Shapes

 ActiveSheet.Shapes...

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

obj-Excel-Worksheet-Shapes

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

Shapes.SelectAll

 ActiveSheet.Shapes.SelectAll

And, the Shapes.SelectAll method selects all the shapes.

Deleting Shapes

Sub...

 ActiveSheet.Shapes.SelectAll
 Selection.Delete

The 4th line deletes all the shape by the Selection.Delete method.

[Application.]Selection

  Selection...

The Application.Selection property returns the selected object.

obj-Excel-Application-Selection

If the shapes are selected on a worksheet, this property returns the ShapeRange object.

And, this ShapeRange object represents all the selected shapes on the active sheet.
So, we do not need to loop through.

The Selection property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-Selection

Selected.Delete (ShapeRange.Delele)

  Selection.Delete

The Selection.Delete (ShapeRange.Delete method) deletes the shapes.
After the shapes are deleted, this macro ends.

Sub DeleteShapes_ActiveSheet()

 ActiveSheet.Shapes.SelectAll
 Selection.Delete

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007