Excel, just like other Office products, supports inserting pictures of various formats into your workbooks. However, deleting them may be time consuming in large workbooks if you're trying to find and delete every single image manually. Let us show you how to make Excel delete all pictures automatically using VBA.
How to make Excel delete all pictures
Pictures are stored in the Pictures collection under a worksheet object. This means that we can loop through pictures in a worksheet and delete them.
For Each pic In ActiveSheet.Pictures
pic.Delete
Next pic
To delete the pictures from the entire workbook, we need to check every sheet.
For Each ws In ActiveWorkbook.Worksheets
For Each pic In ws.Pictures
pic.Delete
Next pic
Next ws
You can use codes in two ways:
- Module
- Immediate Window
In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.
Delete pictures in active sheet
Module method:
Sub DeletePicturesInActiveSheet()
For Each pic In ActiveSheet.Pictures
pic.Delete
Next pic
End Sub
Immediate Window method:
For Each pic In ActiveSheet.Pictures: pic.Delete: Next pic
Delete pictures in selection
Module method:
Sub DeletePicturesInActiveWorkbook()
For Each ws In ActiveWorkbook.Worksheets
For Each pic In ws.Pictures
pic.Delete
Next pic
Next ws
End Sub
Immediate Window method:
For Each ws In ActiveWorkbook.Worksheets: For Each pic In ws.Pictures: pic.Delete: Next pic: Next ws