Macro to delete current row AND delete objects also located in thatrow.

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

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
 
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

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
Click to expand...
 
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
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,
Click to expand...

Tonso
Click to expand...
Click to expand...

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

Tonso
 
Back
Top