View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Deleting shapes in an area

Select a range of cells that includes at least the top left corner of the
shapes you want delete (doesn't have to be contiguous

Sub delete_objects_in_an_area()
Dim shp As Shape
Dim rng as Range, cell as Range
if typename(Selection) < "Range" then
Msgbox "Select a range of cells that contain shapes"
exit sub
End if
set rng = Selection
For Each shp In ActiveSheet.Shapes
set cell = shp.topLeftCell
if not intersect(cell,rng) is nothing then _
shp.Delete
Next
end sub

Or use the control key to select the shapes you want to delete

Sub DeleteSelectedshapes()
Selection.Delete
End sub

or just hit the delete key.

--
Regards,
Tom Ogilvy

"Tim" wrote in message
...
Is there a way to delete shapes/objects from a preselected area? I am
currently using the macro to select whether or not to delete a specific
shape, but this takes a long time as there are many shapes/objects. A

second
problem is that when the dialog box is open, you can't determine which

shape
is selected.
Tim

Sub delete_objects_in_an_area()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Select 'can't determine which shape is selected
response = MsgBox("Delete shape?", vbYesNoCancel + vbCritical +
vbDefaultButton2)
If response = vbYes Then
shp.Delete
ElseIf response = vbCancel Then
Exit Sub
End If
Next
end sub