deleting objects using VBA

[cweijden]

LS,

I'm looking for VBA code with which I can delete shapes (or any othe
object like inserted pictures) when they are/were placed within a rang
of selected cells.

If this doesnot make my problem clear the following description migh
help.
I'd like to select a range of cells (say A3:G50) and be able to delet
any object that is present 'in' this range using VBA code.

Thanks in advance for your help.

Coen van der Weijde

 

[Jake Marx]

Hi Coen van der Weijden,

Here's a subroutine that should do what you're looking for:

Sub RemoveObjectsFromSelection()
Dim ole As OLEObject
Dim shp As Shape

For Each ole In Selection.Parent.OLEObjects
If Not Application.Intersect(Selection, _
ole.TopLeftCell) Is Nothing Then
ole.Delete
End If
Next ole

For Each shp In Selection.Parent.Shapes
If Not Application.Intersect(Selection, _
shp.TopLeftCell) Is Nothing Then
shp.Delete
End If
Next shp
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]