Delete OLEObject from Row or Cell

[Liz]

Hi,
I have seen how you can delete all oleobjects from an active sheet. But I
cannot figure out how to delete an oleobject (embedded file object) from only
the current row or active cell.

For instance, in my worksheet, most rows in columns c have embedded file
objects. I just want a macro to delete the current row's embedded file
object, not all objects in the active sheet.

Is this possible?

Thanks so much,
Liz

 

[Chip Pearson]

Try something like the following:

Dim OleObj As OLEObject
For Each OleObj In Worksheets("Sheet1").OLEObjects
If OleObj.TopLeftCell.Row = ActiveCell.Row Then
OleObj.Delete
End If
Next OleObj

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[OssieMac]

Hi Liz,

Something like the following might help. You could test both Left and Top
position for an object in an individual cell.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

topThisRow = Rows(7).Top
topNextRow = Rows(8).Top

With ActiveSheet
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub

 

[OssieMac]

Hi again Liz,

That was poor coding that I posted even though it will work because working
with the active sheet anyway. Assigning the .Top to the variables should have
been inside the With ActiveSheet then it is transportable to code that
specifies the sheet name instead of ActiveSheet.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

With ActiveSheet
topThisRow = .Rows(7).Top
topNextRow = .Rows(8).Top
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub

 

[Liz]

Thanks so much for your help!!! I wish I had your skill levels!