VBA Excel 2010 Run-time Error 1004 - Application-defined or Object-defined Error

Question

I am a new analyst working on a Macro-enabled worksheet. I ran into some rather popular run-time error while trying to run my macros (i.e., create, delete) multiple times on the same worksheet. I research on the internet and found that the error occurred because my object/variable has not been released every time I re-run the macro. The quick workaround is that I save my worksheet every time I make a change before I run the macros; however, there seems to be a better solution that would get rid of this problem entirely by changing the code to "re-define" my objects every I run the macros. Since I am pretty new to VBA, I am seeking some help with the below lines of code:

Sub Delete_Icons()

  ' INTERSECT COMMAND DETERMINES IF A SHAPE EXISTS WITHIN A SPECIFIED RANGE.

  ' IF THERE IS A SHAPE WITHIN ROWS 3 AND 1000 THEN IT WILL BE DELETED.

  ' THIS DOES NOT DELETE THE MACRO BUTTON IN ROWS 1 AND 2.

    Dim shp As Shape

    For Each shp In ActiveSheet.Shapes

        *' this below line caused run-time error 1004*
        **If Not Intersect(shp.TopLeftCell, Range("A3:F1000")) Is Nothing Then**   
         shp.Delete
        End If
    Next shp

Any help to fix the run-time error in this line of code would be appreciated. Thanks.

If the purpose here is just to avoid deleting some specific shapes then you could simplify things down to just testing the names. So the test within the For Each loop could just be If shp.Name <> "foo" Then — barrowc

Uri Goren Uri Goren · Accepted Answer · 2014-12-31T07:27:59

The Intersect functions is defined for 2 instances of a Range object, and not a Range and a Shape

Try something like this:

Dim shp As Shape, rng As Range
Dim i As Integer, j As Integer

    For i = 1 To ActiveSheet.Shapes.Count
        Set shp = ActiveSheet.Shapes(i)
        For j = 1 To ActiveSheet.Shapes.Range.Count
            Set rng = ActiveSheet.Shapes(i).Range
            ' this below line caused run-time error 1004*
            If Not Intersect(rng, Range("A3:F1000")) Is Nothing Then
             shp.Delete
            End If
        Next j
    Next i

VBA Excel 2010 Run-time Error 1004 - Application-defined or Object-defined Error

1 Answers