Excel Macro: Deleting All Images on Active Sheet

This Excel VBA macro deletes all the images on an active sheet.

Macro Example

Sub DeleteImages_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture Then 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
    If shp.Type = msoPicture Then shp.Delete

The 6th line checks the Shape.Type property. If it is the msoPicutre then it will be deleted by the Shape.Delete method.

obj-Excel-Shape-Delete

Loops Back

Sub...

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture Then shp.Delete
  Next shp

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

After every image is deleted, this macro ends.

Sub DeleteShapes_ActiveSheet()

  Dim shp As Shape

  For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture Then shp.Delete
  Next shp

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007