Delete Shapes in a Range

[ultrawat]

Hope someone can help.....

I have a worksheet "Proposal" which has some forms buttons, some forms 'tick boxes' and some rectangles drawn with the Drawing rectangle tool.

I want to use a macro to delete any and all of the above which fall into the Range A5:AE190

I have tried:

Sub ShapesInRangeDelete()

Dim sh As Shape
Dim ws As Worksheet
Set ws = Worksheets("Result")
Set Rng = ws.Range("A5:AE190")

For Each sh In ws.Shapes
If Not Application.Intersect(sh.TopLeftCell, Rng) Is Nothing Then
sh.Delete
End If
Next sh
End Sub


...but get a an "Application defined or Object defined error" on the "If Not...." line.

Can someone offer some suggestions.......

Thanks in advance

 

[Tom Urtis]

Your code almost works but you made a couple errors.

First, you say in your opening text that your sheet of interest is named "Proposal" but your macro is coded for a sheet named "Result".

Second, you did not declare the variable Rng.

Third, you should set the ws and Rng variables to Nothing to release them from memory, though that did not contribute to your error.

This amended code works for me if the worksheet of interest really is named "Result":




Sub ShapesInRangeDelete()
Dim Rng As Range
Dim sh As Shape
Dim ws As Worksheet
Set ws = Worksheets("Result")
Set Rng = ws.Range("A5:AE190")
For Each sh In ws.Shapes
If Not Intersect(sh.TopLeftCell, Rng) Is Nothing Then sh.Delete
Next sh
Set Rng = Nothing
Set ws = Nothing
End Sub