Deleting All Pictures

Theresa inherited a large number of workbooks, each including quite a few pictures. She needs to delete the pictures, but doing so one at a time is rather tedious. Theresa wonders if there is a way to open a workbook and quickly delete all the pictures rather than selecting them one at a time.

There are a couple of ways you can approach this task. One way to accomplish it is to follow these steps:

1. Press F5. Excel displays the Go To dialog box.
2. Click the Special button, in the lower-left corner. Excel displays the Go To Special dialog box.
3. Click the Objects radio button.
4. Click OK. Excel selects all the pictures in the worksheet.
5. Press the Delete key. All the pictures are deleted.

Another way to do this is to click on one of the pictures so it is selected (handles appear around it), and then press Ctrl+A. That selects all the pictures and you can press the Delete key.

There are few major caveats to using this method of selecting and deleting. First, it works only in the currently active worksheet. Theresa wanted to delete all the pictures in the workbook, which means she would need to repeat these steps for each worksheet. Second, note that you are selecting objects, which is not limited to pictures. If the worksheet contains other objects such as shapes and embedded charts, those are selected (and subsequently deleted) as well.

Even if you only have pictures on the worksheet, deleting them using these steps only relieves some of the tedium that Theresa notes. A good way to remove the tedium is to use a macro to do the deletions. This macro will delete all the pictures on all worksheets in the current workbook.

Sub DeleteWorkbookPictures1()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Pictures.Delete
    Next ws
End Sub

Notice that the macro uses the Pictures collection. Documentation on this collection is spotty, at best, and it is not clear whether Microsoft officially recommends its use. What is clear is that each worksheet has a Pictures collection, and that collection is a subset of the larger Shapes collection.

This means that you could rewrite the macro to utilize the Shapes collection, if you prefer to stick to "official" collections. Here is a macro that does the exact same thing as the previous one:

Sub DeleteWorkbookPictures2()
    Dim ws As Worksheet
    Dim shp As Shape

    For Each ws In ActiveWorkbook.Worksheets
        For Each shp In ws.Shapes
            If shp.Type = msoPicture Then shp.Delete
        Next
    Next ws
End Sub

The macro steps through the Shapes collection on each worksheet and checks the .Type property of the shape. If the shape is a picture (msoPicture), then the shape is deleted.

If you want even more automation in the macro, you can step through all the workbooks in a folder and delete all the pictures. Here's how to accomplish the task:

Sub Delete_All_Pics_In_Folder()
    Dim sPath As String
    Dim sFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim shp As Shape
    Dim sMsg As String
    Dim iCount As String

    sPath = "C:\YourFolderPath\"
    sMsg = ""

    sFile = Dir(sPath & "*.xls*")
    Do While sFile <> ""
        Set wb = Workbooks.Open(sPath & sFile)

        iCount = 0
        For Each ws In wb.Worksheets
            For Each shp In ws.Shapes
                If shp.Type = msoPicture Then
                    shp.Delete
                    iCount = iCount + 1
                End If
            Next shp
        Next ws

        wb.Close SaveChanges:=True

        If iCount > 0 Then
            sMsg = sMsg & "Deleted " & iCount & " pictures in " & sFile & vbCr
        End If
        sFile = Dir
    Loop
    MsgBox sMsg
End Sub

The key to making this macro work is to copy all the workbooks you want to process into a single folder. Then, change sPath so that it contains the full path to the folder. It is very important that the path end with a backslash. The macro then uses the Dir function to return the names of each workbook in the folder. (A workbook, for this macro, is defined as any file that uses an extension that begins with .xls.)

The macro opens the workbooks, steps through each worksheet, checks each member of the Shapes collection, and deletes the shape if it is a picture. When all the worksheets in a workbook have been processed, the macro then saves the workbook and moves on to the next one. When all the workbooks have been processed, then the macro displays a message telling how many pictures were deleted in each workbook.

There is one other non-macro approach that should be mentioned before finishing out. If you prefer to not delete the pictures, you could simply hide them. This is done by configuring Excel to hide them, as discussed in this ExcelTip:

https://tips.net/T6124

The pictures remain in the workbook, they are simply hidden from view.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13949) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.