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