How to Delete All Pictures or Charts on a Worksheet

Deleting a single chart is a right-click. The problem starts when a sheet has picked up dozens of pasted images, or a report generates a chart per region and you need them all gone before the next run.

Delete one chart

Click its border — not the plot area inside it — so the whole chart object is selected, then press Delete. If you click inside first, you select an element of the chart and Delete removes only that element. Telling the chart object apart from its elements is exactly the sort of detail the MOS Excel Associate exam tests. Press Escape once to step out to the chart object.

Delete everything at once, without VBA

Excel can select every floating object on a sheet in one step:

1. Press F5 (or Ctrl+G) to open Go To
2. Click Special…
3. Choose Objects
4. Press Delete

The keyboard route is Alt then F5, S, B, Enter — worth learning if you do this often.

Be aware of what “objects” includes. This selects charts, pictures, shapes, text boxes, SmartArt, form controls, ActiveX controls and slicers. If your sheet has buttons that run macros, or a slicer connected to a PivotTable, this removes those too. There is no way to filter the selection in the dialog.

If the reason you are deleting a chart is that it stopped matching its data, you usually do not need to rebuild it: extending the chart’s source range is a ten-second fix.

Ctrl+Z does undo it, so the damage is recoverable if you notice immediately. If you only want something out of sight rather than gone for good, hiding cell contents and formulas is the safer option.

Delete only the pictures, or only the charts

This is where VBA earns its place, because Go To Special cannot distinguish between object types. Once you are scripting the sheet anyway, the same approach handles formatting jobs such as putting borders around all used cells.

Charts only:

Sub DeleteAllCharts()
    Dim cht As ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Delete
    Next cht
End Sub

Pictures only:

Sub DeleteAllPictures()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Type = msoPicture Then shp.Delete
    Next shp
End Sub

Everything floating on the sheet:

Sub DeleteAllShapes()
    ActiveSheet.Shapes.SelectAll
    Selection.Delete
End Sub

The Shapes collection covers all of it — charts included, since a chart on a worksheet is a shape wrapping a ChartObject. That is why the picture macro tests shp.Type: without the test it would take your charts as well.

Other type constants you may want: msoTextBox, msoFormControl, msoOLEControlObject and msoChart.

Keep one, delete the rest

Give the one you want to keep a name — select it, type the name in the Name Box left of the formula bar, press Enter — then:

Sub DeleteAllShapesExceptOne()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Name <> "KeepThis" Then shp.Delete
    Next shp
End Sub

Across every sheet

Sub DeleteAllPicturesAllSheets()
    Dim ws As Worksheet, shp As Shape, i As Long

    For Each ws In ThisWorkbook.Worksheets
        For i = ws.Shapes.Count To 1 Step -1
            If ws.Shapes(i).Type = msoPicture Then ws.Shapes(i).Delete
        Next i
    Next ws
End Sub

Note the backwards loop, Step -1. Deleting from a collection while iterating forward with For Each shifts the remaining items down and the loop skips every second one — a bug that leaves roughly half the pictures in place and looks like the macro simply did not work. Counting down avoids it.

If nothing gets deleted

The sheet is protected. Protection blocks object deletion. Unprotect it, or add ActiveSheet.Unprotect at the start and re-protect afterwards.

The image is not an object at all. Pictures inserted into a header or footer are not shapes and will not appear in the Shapes collection or in Go To Special. Remove those via Page Layout → Print Titles → Header/Footer.

It is a background image. Set through Page Layout → Background, this is a sheet property rather than an object. Remove it with the Delete Background button that appears in the same place.

One last thing worth knowing: macro deletions cannot be undone with Ctrl+Z. Save the file before running any of these for the first time.