CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel select & delete all shapes with the same ID and remove

I would like to delete all the shapes from my sheet. They have the same ID.

enter image description here

I found two codes:

The first one:

Public Sub ActiveShapes()
    Dim ShpObject As Variant

    If TypeName(Application.Selection) = "Firestop" Then
        Set ShpObject = Application.Selection
        ShpObject.Delete
    Else
        Exit Sub
    End If
End Sub

is not working. There are no errors, but also no reaction at all.

The second one: Selecting a shape in Excel with VBA

 Sub Firestopshapes()
     ActiveSheet.Shapes("Firestop").Delete
 End Sub

works, but remove only one by one element. In my event, all the elements have the "Firestop" ID. I would like to make them all deleted at once. How can I do that?

Upvotes: 1

Views: 2193

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue is thet If TypeName(Application.Selection) = "Firestop" Then is never true. Have a look into the TypeName function does not return the name of the Application.Selection but instead it returs what type Application.Selection is. Here it probably returns Object because a shape is an object.

Actually names are unique. You cannot add 2 shapes with the same name. That is why ActiveSheet.Shapes("Firestop").Delete only deletes one shape.

There seems to be a bug that when you copy a named shape 2 shapes with the same name exist (which should not be possible). You can get around this by deleting that shape in a loop until you get an error (no shape with that name is left).

On Error Resume Next
Do
    ActiveSheet.Shapes("Firestop").Delete
    If Err.Number <> 0 Then Exit Do
Loop
On Error GoTo 0 'don't forget this statement after the loop

Upvotes: 5

Dy.Lee
Dy.Lee

Reputation: 7567

It is not recommended to use On Error Resume Next often. We recommend using it only when it is indispensable.

Sub test()
    Dim shp As Shape
    Dim Ws As Worksheet

    Set Ws = ActiveSheet
    For Each shp In Ws.Shapes
        If shp.Name = "Firestop" Then
            shp.Delete
        End If
    Next shp
End Sub

Upvotes: 3

Related Questions