Deleting Lines and Rectangles

[adrian.groves]

Hi,

I have a need to delete many shapes on a worksheet although I wish to keep others. All of the shapes that I wish to delete are either lines or rectangles and the ones that I wish to keep are not. I had hoped the following would work, but it doess not - can anybody let me know what the correct code should be please?

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.LineShape = Line Then shp.Delete
If shp.LineShape = Rectangle Then shp.Delete
Next shp

​

Thanks

 

[Fluff]

How about

Sub adriangroves()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
  If Shp.AutoShapeType = -2 Or Shp.AutoShapeType = 1 Then Shp.Delete
Next Shp
End Sub

 

[adrian.groves]

Thanks Fluff,

That is almost there but it also deleted the text boxes and curly brackets that I had. I played around and noted the -2 was the problem..... do you have any further suggestions.

Thanks

 

[Fluff]

It shouldn't have delete the brackets, but the textbox yes.
Have you renamed any of the shapes?

 

[adrian.groves]

Hi, No I have not renamed any.

 

[adrian.groves]

If it makes any difference, I had drawn the lines using the following VBA code

Shapes.AddLine(Startx, Starty, Endx, Endy)

and drawn the rectangles using

Shapes.AddShape(1, .....

 

[Fluff]

Ok, how about

Sub adriangroves()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
   If Shp.Name Like "Straight*" Or Shp.Name Like "Rectangle*" Then Shp.Delete
Next Shp
End Sub

 

[adrian.groves]

That works perfectly.

Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback