Is there a way to, when a macro deletes the current row, to also have
it find and delete any drawn objects [circles, rectangles, etc]. when
my macro deletes a row, a remnant of the drawn object, which appears
as a line, remains.
I am using XL 2002.

Thanks,

Tonso

The drawing object is not part of the row so won't be deleted with the
row(s)

Set its properties to "move but not size with cells" and it won't shrink up
to a line when you delete rows.


Gord Dibben MS Excel MVP

On Mon, 26 Jan 2009 13:41:49 -0800 (PST), Tonso <(E-Mail Removed)>
wrote:

>Is there a way to, when a macro deletes the current row, to also have
>it find and delete any drawn objects [circles, rectangles, etc]. when
>my macro deletes a row, a remnant of the drawn object, which appears
>as a line, remains.
>I am using XL 2002.
>
>Thanks,
>
>Tonso

Maybe...

First, Ron de Bruin has a lot of info on this page:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

There are a lot of shapes you do not want to delete (data|validation dropdowns,
arrows from autofilters, comments and any more you can think of...)

Option Explicit
Sub testme()

Dim shp As Shape
Dim testStr As String
Dim RowToDelete As Long
Dim TopLeftCellRow As Long
Dim OkToDelete As Boolean

RowToDelete = 3

For Each shp In ActiveSheet.Shapes
TopLeftCellRow = 0
On Error Resume Next
TopLeftCellRow = shp.TopLeftCell.Row
On Error GoTo 0
If TopLeftCellRow = 0 Then
'not a shape to delete, so skip it
Else
If TopLeftCellRow = RowToDelete Then
OkToDelete = True
If shp.Type = msoComment Then
OkToDelete = False
ElseIf shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
'it might be a data|validation dropdown
testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0
If testStr = "" Then
OkToDelete = False
End If
End If
End If
If OkToDelete Then
shp.Delete
End If
End If
End If
Next shp
End Sub


Tonso wrote:
>
> Is there a way to, when a macro deletes the current row, to also have
> it find and delete any drawn objects [circles, rectangles, etc]. when
> my macro deletes a row, a remnant of the drawn object, which appears
> as a line, remains.
> I am using XL 2002.
>
> Thanks,
>
> Tonso

--

Dave Peterson

On Jan 26, 6:04*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Maybe...
>
> First, Ron de Bruin has a lot of info on this page:http://www.rondebruin.nl/controlsobjectsworksheet.htm
>
> There are a lot of shapes you do not want to delete (data|validation dropdowns,
> arrows from autofilters, comments and any more you can think of...)
>
> Option Explicit
> Sub testme()
>
> * * Dim shp As Shape
> * * Dim testStr As String
> * * Dim RowToDelete As Long
> * * Dim TopLeftCellRow As Long
> * * Dim OkToDelete As Boolean
>
> * * RowToDelete = 3
>
> * * For Each shp In ActiveSheet.Shapes
> * * * * TopLeftCellRow = 0
> * * * * On Error Resume Next
> * * * * TopLeftCellRow = shp.TopLeftCell.Row
> * * * * On Error GoTo 0
> * * * * If TopLeftCellRow = 0 Then
> * * * * * * 'not a shape to delete, so skip it
> * * * * Else
> * * * * * * If TopLeftCellRow = RowToDelete Then
> * * * * * * * * OkToDelete = True
> * * * * * * * * If shp.Type = msoComment Then
> * * * * * * * * * * OkToDelete = False
> * * * * * * * * ElseIf shp.Type = msoFormControl Then
> * * * * * * * * * * If shp.FormControlType = xlDropDown Then
> * * * * * * * * * * * * 'it might be a data|validation dropdown
> * * * * * * * * * * * * testStr = ""
> * * * * * * * * * * * * On Error Resume Next
> * * * * * * * * * * * * testStr = shp.TopLeftCell.Address
> * * * * * * * * * * * * On Error GoTo 0
> * * * * * * * * * * * * If testStr = "" Then
> * * * * * * * * * * * * * * OkToDelete = False
> * * * * * * * * * * * * End If
> * * * * * * * * * * End If
> * * * * * * * * End If
> * * * * * * * * If OkToDelete Then
> * * * * * * * * * * shp.Delete
> * * * * * * * * End If
> * * * * * * End If
> * * * * End If
> * * Next shp
> End Sub
>
> Tonso wrote:
>
> > Is there a way to, when a macro deletes the current row, to also have
> > it find and delete any drawn objects [circles, rectangles, etc]. when
> > my macro deletes a row, a remnant of the drawn object, which appears
> > as a line, remains.
> > I am using XL 2002.
>
> > Thanks,
>
> > Tonso
>
> --
>
> Dave Peterson

Thanks! This macro should do what i need. And thanks for introducing
me to Ron's info.

Tonso