check if shape exists, then...

**Armitage2k** · 10-26-2010, 03:33 AM

Hi!

I am currently using the following macro to delete all shapes in a range on my worksheets. However, recently I happen to run into error 1004 telling me that an object is missing... Interestingly enough, when I end the complete macro (of which this one is a part of, basically a whole bunch of individual macros all called one after another) and then call this specific macro by itself, it seems to work with no problem.

Sub DeleteAllPics()

Dim ws As Worksheet
    For Each ws In Worksheets
            With ws
               Set rngCheck = .Range("A3:A50")
               For Each Shp In .Shapes
                  ' check if shape is located in our range
                  If Not Intersect(Shp.TopLeftCell, rngCheck) Is Nothing Then
                     'Delete
                     Shp.Select
                     Selection.Delete
                  End If
               Next Shp
            End With
        Next ws

End Sub

Well, since I cannot figure out what is going on, I would like to modify the macro above to firstly only run if the sheet name is either "VIP" - "PCP" - "LSG" or "Company", and secondly check if there actually IS any shape in the specified range before trying to delete it.

Since I did not write this code, and I am not familiar with shapes in vba, I was wondering if someone could give me a hand on this?
Thanks a lot!

A2k

**romperstomper** · 10-26-2010, 04:02 AM

You already are checking if the shape is in that range so I'm not sure what you mean? There's no need to select the shape before deleting it though.

**Armitage2k** · 10-26-2010, 05:26 AM

well, technically its defining the remaining procedure for each shape in range. my guess is that I am experiencing troubles which somehow are related to the case if there is no shape in range, since I am getting a 1004 error, which is related to a missing object.

This line is highlighted in the editor:

If Not Intersect(Shp.TopLeftCell, rngCheck) Is Nothing Then

I already deleted all shapes in the form but cannot get past this error. I am just thinking that by more strictly defining the sheets it is supposed to run and to specifically let it check if there really is a shape in the range.

Any ideas what that could be?
Thanks,
A2k

**romperstomper** · 10-26-2010, 05:31 AM

Well, I would always recommend looping backwards when deleting:

Sub DeleteAllPics()
    Dim n As Long
    Dim ws As Worksheet
    Dim rngCheck As Range
    For Each ws In Worksheets
        With ws
           Set rngCheck = .Range("A3:A50")
           For n = .Shapes.Count To 1 Step -1
              ' check if shape is located in our range
              If Not Intersect(.Shapes(n).TopLeftCell, rngCheck) Is Nothing Then
                 'Delete
                 .Shapes(n).Delete
              End If
           Next n
        End With
    Next ws
End Sub

**Armitage2k** · 10-26-2010, 07:03 AM

still the same error: runtime error 1004 - application defined or object defined error.

I seriously have no idea what that could be. It now doesnt work anymore no matter if it is started as part of the whole macro procedure or as an individual macro. Can I somehow skip this step if it returns an error?