Delete all Excel drawing layer shapes matching a pattern

Consider an Excel Worksheet with lots of shapes. To select two shapes:

ActiveSheet.Shapes.Range(Array("Freeform 314", "Freeform 278")).Select

The goal is to delete all the shapes starting with "Freeform".

I found an example referencing MS VBScript Regular Expression 5.5, but shapes do not reside on specific cells. The quantity or specific number of shapes is almost unkwown. So an ugly solution can be to brute force the strings names, on the other hand I hope you can help me out to achieve an elegant solution.

The code below doesn't work, but you can get a better idea of the problem.

Sub DeleteShapes()

    Dim re As New RegExp
    re.Pattern = "Freeform*"
    Dim cell As Range
    For Each cell In ActiveSheet.Shapes.Range(Array(re.Pattern)).Select
       Selection.delete
    Next cell
End Sub

Answers


Seems like this would be far far easier if you went

Dim shape as Variant
For each shape in ActiveSheet.Shapes
   If instr(1, shape.Name, "Freeform") <> 0 then shape.delete
Next shape

Tested it and it works. It takes the whole collection of shapes on the active sheet, and for each one, it deletes it if the word "Freeform" is in the shape name.

If you want to specifically delete only shapes starting with "Freeform", then instead of testing: instr(1, shape.Name, "Freeform") <> 0 You should use: instr(1, shape.Name, "Freeform") = 1


You don't need a regular expression at all to find the Freeform shapes.

Dim shape As Excel.shape

For Each shape In ActiveSheet.Shapes

    If Left(shape.Name, 8) = "Freeform" Then
        shape.Delete
    End If

Next

Need Your Help

Scaling data in R ignoring specific columns

r

I have some data in csv format I want to use for predictive modeling. I read the data in R and apply some simple preprocessing (ommitting NA etc.). Before I want to train a SVM classifier I want to...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.