VBA - Remove All Shapes In a Sheet

Hi. This post is a result of recent situation I faced. I copied contents of a webpage and pasted into my Excel workbook for further analysis. All was good until I noticed many tiny little checkboxes / shapes that were part of webpage also got pasted into the workbook. I could have used Paste Special command to paste only text but I also need the formats, however that’s not the point. We are trying to delete all shapes in a worksheet with single operation. There are few ways of deleting these shapes in Excel but all involve more than one click. Let’s see how to do this by way of a macro; it will also give us some more learning in to the coding world. Following our standard from earlier posts, we will start by recording a macro while deleting a shape and analyse the code.

Record the code

Open the workbook where the shape / picture (s) are inserted (if you don’t have, you can start by inserting a shape / picture of your choice in the workbook). For easy reference, I have a workbook in which I have various shapes ranging from a rectangle, a clip art, a star & a form button.

Now, let’s record a macro by deleting any one shape and see the code. (How to record a macro & how to access the recorded code)

Below is the code VBA shown me when I deleted the rectangle.

Sub Macro1()
'
' Macro1 Macro
'
 '
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    Selection.Delete
End Sub

By analysing the above code, we could see ActiveSheet (a sheet that is active at the time of macro being recorded) has a property called Shapes and one of the shapes named Rectangle 1 was selected and deleted.

Let’s use this information. If you are looking to delete all the shapes in active sheet you can straight away use ActiveSheet object. However, if you are looking to apply this macro to a different sheet, you already know how to reference different sheet(s). We saw that Shapes object contains the collection of all shapes in the sheet, since we need not worry about a particular shape in our sheet (we are trying to delete all, remember), we just need to delete whatever is there in the Shapes object.

A quick look in VBA help on Shapes object will reveal the members of Shapes object. One of such member is SelectAll method which as the name implies, will select collection of the shapes that are part of Shapes object. Last line of above code will then be used to delete all the selected shapes.

So our first cut code looks something like below.

Sub Test()
ActiveSheet.Shapes.SelectAll ‘Selects all the shapes in active sheet
Selection.Delete ‘Delete the selected shapes
End Sub

Execute this macro and check. All the shapes in your active sheet must be gone. Clear?

You can apply the above code for all sheets in entire workbook as below.

Sub Test2()Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Shapes.SelectAll
    Selection.Delete
Next Sht
End Sub

There is another way you can delete all shapes in a sheet by using For Each loop. Check out the below code.

Sub Test3()
Dim Shp as Shape
For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp
End Sub

I am assigning each shape to a variable and looping through all the shapes and deleting each one of them. However, in some versions of Excel, above code was said to remove the comments and AutoFilter as well. If you face such an issue, you can use the below code.

Sub Test4()
On Error Resume Next
ActiveSheet.DrawingObjects.Delete
End Sub

If you are deleting all the shapes, our first code is efficient however the second code will come to your rescue when you have to delete only certain types of shapes or only particular shapes.

Delete Specific Shapes

You have many types of shapes in your sheet and want to delete only certain type(s). For example: You want to delete all of the text boxes in the sheet.

Prerequisite to code above requirement is to know that each Shape in Excel contains a Type property which determines if a particular shape is text box or Image or a button etc.

By looking at the Type property values, you can notice text box type under Shapes is msoTextBox (also represented by value 17).  You can look at all the type object values in Microsoft Developer Network

Now, we just need to loop through the Shapes and see if any Shape Type is msoTextBox. If yes, then delete that Shape otherwise move to next shape. Simple? Let’s code it.

Sub test()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoTextBox Then Shp.Delete
Next Shp
End Sub

Delete All but One Shape

Assume a situation where you have to delete all shapes but leave only one shape named Check Box 1. You cannot obviously use SelectAll method hence the above code with IF Then Else statement would help you. See the below code.

Sub Test5()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
    If Shp.Name <> "Check Box 1" Then Shp.Delete
Next Shp
End Sub

Look at the Shape object members. Read about the methods and properties supported by Shape object. You will master playing with all and all shapes in your workbook. Go on and do experiments with what all you can do using Shapes object. As I mentioned in one of previous post, learning VBA is all about how much you consult VBA help, everything you need to learn in VBA is out there. Happy Learning!!

PS: If you are trying to delete all the objects in an Excel sheet, you can do that without using VBA. Clicking on Go To Special > Objects will select all the objects and you can use Delete button to delete all of them in one go. You might also want to read our post on Working with Shapes.