Deleting pictures with Excel VBA

Question

How do I delete all the pictures in an Excel 2007 worksheet? A working code example would be great.

score 22 · Answer 1 · answered Aug 10 '15 at 12:13

22

The simplest way:

Activesheet.Pictures.Delete

or

Activesheet.Shapes.Delete

Depending on the type of object your picture is.

Deletes all pictures with greater efficiency then iterating (looping through) and deleting them one by one.

answered Aug 10 '15 at 12:13

TCN

1,571
1
26
46

4

`Activesheet.Shapes.Delete`throws an error (object doesn't support this method) – Patrick Lepelletier Jan 25 '16 at 00:00

score 13 · Accepted Answer · answered Dec 18 '10 at 21:26

13

Dim shape As Excel.shape

For Each shape In ActiveSheet.Shapes
        shape.Delete
Next

answered Dec 18 '10 at 21:26

Eric Fortis

16,372
6
41
62

Note that this deletes PivotCharts (and probably anything else that you select like a picture). – Arlen Beiler Dec 20 '10 at 13:43
You are right, to prevent it, assuming that you are using the default chart names ("Chart 1", "Chart 2",...), you can test inside the loop with the inString function e.g. InStr(0,shape.name,"Chart"), so if doesn't contains the "Chart" in the name skip it. – Eric Fortis Dec 20 '10 at 18:00
6

works not, because it will also delete all comments,buttons... and other stuff wich are also Shapes ! A simple `activesheet.pictures.delete` would do. Or you can use the Loop and add a condition : `if shape.type=msopicture or shape.type=msolinkedpicture or shape.type=msoEmbeddedOLEObject then shape.delete` – Patrick Lepelletier Nov 12 '14 at 12:00
here is a list of all objects considered as Shapes : http://msdn.microsoft.com/en-us/library/aa432678(v=office.12).aspx – Patrick Lepelletier Nov 12 '14 at 12:02

score 2 · Answer 3 · answered Nov 21 '17 at 17:41

To delete all pictures or others shapes, you can iterate all of them and check the type:

Dim shape As Excel.shape

For Each shape In ActiveSheet.Shapes

    Select Case shape.Type
        Case msoPicture, msoMedia, msoShapeTypeMixed, msoOLEControlObject, msoAutoShape
            shape.Delete
        Case Else
            'Do nothing
    End Select
Next

In my case this code was usefull because my sheet was full of transparent shapes of type msoAutoShape which I thought were pictures. So, Activesheet.Pictures.Delete was not working.

You can find all shape types on this link: http://msdn.microsoft.com/en-us/library/aa432678(v=office.12).aspx

Deleting pictures with Excel VBA

3 Answers3

Linked