Deleting all pictures / shapes in a range

[toboggan]

I've been searching and searching to find an answer for this and, while I've found many answers, none of them seem to be working for me. I'm trying to copy several rows and then paste them below. There is a specific area that will sometimes have pictures and/or shapes that I don't want to copy down to the newly created rows. So I'm trying to select the shapes within the newly created area and delete them.

Most of the code I've found so far seem to be some variation of this:

Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
            If Not Intersect(Range("B454:AA480"), sh.TopLeftCell) Is Nothing And _
               Not Intersect(Range("B454:AA480"), sh.BottomRightCell) Is Nothing Then
                sh.Delete
            End If
        Next sh

However, this code is giving me Run-time error '1004': Application-defined or object-defined error.


Weird thing is. I ran it once and it seemed to work, but if I delete the rows that were created and run the macro again, I continuously get the error above.

 

[toboggan]

Right, but it's not within the range. Additionally, it appears as if when the drop down boxes are filled in with data, it works, but when one of the boxes is blank, it gives an error. Further, if it's blank and you get an error once, it will continue to give that error even after filling it in. I'll see if I can fix it by putting data in the box, then run the code that deletes the shapes, and then delete the data in the box.

 

[Andrew Poulsom]

Maybe you could test that the shape's Type is not msoFormControl (8).

 

[toboggan]

How do I do that?

 

[Andrew Poulsom]

Maybe:

    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
            If sh.Type <> msoFormControl Then
                If Not Intersect(Range("B454:AA480"), sh.TopLeftCell) Is Nothing And _
                   Not Intersect(Range("B454:AA480"), sh.BottomRightCell) Is Nothing Then
                    sh.Delete
                End If
            End If
        Next sh

 

[toboggan]

Well that works perfectly!

That's pretty awesome.


Thanks so much!