Excel delete all pictures or delete all pictures in specific Cells

This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells.

Excel delete all pictures

When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to  delete all pictures at once.

Let’s say I want to copy the below table from a website. In the Country of Origin field, each country has a logo of the country.

 

When we copy the table to Excel, the country logos are also copied.

 

Press F5 > Special > Objects > OK

Now all the pictures are selected, click on keyboard Delete button to delete all pictures.

Excel VBA delete all pictures

Press ALT+F11, insert the below code in a Module

Public Sub delete_picture()
 For Each shp In ActiveSheet.Shapes
 shp.Delete
 Next
End Sub

Run the Macro, now all the pictures are deleted.

Alternatively, you can use DrawingObjects.Delete

Sub delete_picture2()
 ActiveSheet.DrawingObjects.Delete
End Sub

As you can notice in the VBA code, DrawingObjects have Delete Method, but not Shapes. If you want to use For Loop to loop through each object, you have to use Shapes.

Excel VBA delete all pictures in specific Cells

I haven’t found a way to delete pictures in specific Cells without using VBA, so I believe VBA is the way to go.

Lets say we want to delete pictures in specific Cells, only pictures in Range E2:E9.

Use TopLeftCell Property to determine if a Cell contains a picture. Click here to learn more about the Intersect Method.

Public Sub delete_picture()
 For Each shp In ActiveSheet.Shapes
 If Not Intersect(shp.TopLeftCell, [E2:E9]) Is Nothing Then shp.Delete
 Next
End Sub

Run the Macro to get the below result

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *