Deleting all images in a worksheet

[jungix]

Hi,

I know how to delete one image that I see in a worksheet, but how can I manage to delete all images on a worksheet, knowing that I don't know how many there are and what their number are?

Jungix

[Bob Phillips]

You need to be careful

Sub DeleteShapes()
Dim oShape As Shape
Dim sTest As String
Dim OKToDelete As Boolean
For Each oShape In ActiveSheet.Shapes
    OKToDelete = True
    sTest = ""
    On Error Resume Next
    sTest = oShape.TopLeftCell.Address
    On Error GoTo 0
    If oShape.Type = msoFormControl Then
        If oShape.FormControlType = xlDropDown Then
            If sTest = "" Then
                 'keep it
                 OKToDelete = False
            End If
        End If
    End If
    If OKToDelete Then
        oShape.Delete
    End If
Next shp
End Sub

[jungix]

Thanks, with Next oShape it worked on my sample.

Why did you say I should be careful? What is your sTest doing?

[Bob Phillips]

Thanks, with Next oShape it worked on my sample.

Why did you say I should be careful? What is your sTest doing?

Because the easy way is to go

For Each oShape In ActiveSheet.Shapes
    oShape.Delete
Next oShape

but the problem here is that it also removes the Data Validation dropdown and the Autofilter dropdown, and there is no way to get them back. I developed the convoluted method shown previously to avoid this.

[matthewspatrick]

but the problem here is that it also removes the Data Validation dropdown and the Autofilter dropdown, and there is no way to get them back.

That's a pretty significant issue!

[Bob Phillips]

That's a pretty significant issue!

Slight correction, you can re-create the autofilter which will restore that arrow, that's a pain but it can be done, but I know of no way to get the DV arrow back. The even worse part of that is that it is gone from the sheet, thaht is if you create a new List DV on that sheet, it too will not have an arrow.