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
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