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”
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.
Step 2
Insert a module in which we will write the code to delete the shapes based on names.
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.
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.
Hope this helps.
