Excel VBA To Delete Shape Whose Name Starts With Certain String

In this post we will see how to delete a shape in excel whose name starts with a certain string.

I am going to explain how we could delete a shape/object in excel vba on the basis of the name of the shape/object.

We could delete a shape or image based on their name, there may be multiple situation in which we would like to leverage the names of the shape/image/object, e.g. we could delete shape if the name of the shapes has certain string I it or if the name of the shape ends with certain string.




As seen in the pic below, we have 3 shapes with different names and we would like to delete the shapes whose name starts with string “Delete”

Image showing excel shapes with names that are to be deleted

Rectangle’s Name-“Delete_Rectangle”

Oval’s Name-“Delete_Oval”

Triangle’ Name-“Triangle”

Our objective is to delete the shapes in the excel sheet whose name starts with “Delete”, we will write a vba code to delete the shapes based on names.

Step 1

We will create a macro in excel, open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to delete shape on the basis of names

Step 2

Insert a module in which we will write the code to delete the shapes based on names.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.




Sub Delete_Shapes()

Dim shp As Shape

For Each shp In Worksheets(“Sheet1”).Shapes

    If shp.Name Like “Delete*” Then

        shp.Delete

    End If

Next shp

End Sub

 This program has loop which will check the names of each shapes and if the name has certain string in ti, it will delete that shape.

VBA code to delete shapes whose name starts with certain string

Step  4

Run the program by pressing F5 or by play button.

You can modify the above program to suit your requirement, you can change the string in the IF condition.

You can see that after the program is run, shapes whose name starts with “Delete” have been deleted and only the triangle (name “Triangle” doesn’t have “Delete” in it) is left in Sheet1.




Excel showing shapes that are not deleted as they do not have certain string in their name

Hope this helps.

Share The Knowledge

Random Posts