VBA type mismatch error when deleting pictures

Question

Trying to delete all pictures in a range. I found the below code online but its throwing a type mismatch error on row:

For Each pic In ws.Pictures

Any input or alternative code is much appreciated.

Sub clearPics()
Dim s As String
Dim pic As Picture
Dim rng As Range

Set ws = Worksheets("sort")
Set rng = ws.Range("c:c")

For Each pic In ws.Pictures
    With pic
        s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
    End With
    If Not Intersect(rng, ws.Range(s)) Is Nothing Then
        pic.Delete
    End If
Next

End Sub

Thanks, jj

I always struggle with this kind of code, but does changing it to `Dim pic as Shape` fix it? — Doug Glancy, Mar 28 '17 at 19:58
The Microsoft worksheet.pictures documentation shows it being object. I would change it to Dim pic as Object. https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.pictures(v=vs.120).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 — PKatona, Mar 28 '17 at 20:00
@Jeeped, I was hoping that the `In ws.Pictures` part would deal with that, but totally untested so I don't know. — Doug Glancy, Mar 28 '17 at 20:01
@PKatona changing to Object worked. But it is also deleting radio buttons, drop downs and checkboxes on the sheet. How do you delete only pictures? — joell, Mar 28 '17 at 20:15
http://stackoverflow.com/questions/2090578/ms-access-determine-object-type — PKatona, Mar 28 '17 at 21:17

score 0 · Answer 1 · answered Mar 28 '17 at 20:01

0

You have declare pic as Picture. Rename Dim pic to something else like Dim pic1 and try it now. Or just remove that line.

What i believe is happening is that pic is of type picture. But what every ws.pictures is returning as a type doesn't equal picture

answered Mar 28 '17 at 20:01

Mohammad C

1,321
1
8
12

True 'dat. I found that simply removing the typedef and changing `Dim pic As Picture` to `Dim pic` or `Dim pic As Variant` works out. – Mar 28 '17 at 20:08
Tried but no change – joell Mar 28 '17 at 20:15
Have you tried removing `Dim pic as Picture` completely – Mohammad C Mar 28 '17 at 20:16
Removing completely worked. But again all form controls are also being deleted, i want to delete only pictures. – joell Mar 28 '17 at 20:27
i have no experience in vba in excel. but try `For Each pic In ws.Shapes` maybe there is a way to check if a shape is a picture. as i think this will delete any shape you ask it to and shape probably includes picture, diagram and charts etc. – Mohammad C Mar 28 '17 at 21:01

score 0 · Answer 2 · answered Mar 28 '17 at 23:16

The below code deletes only pictures in the sheet. By checking Shape.Type = 11 for pictures.

Sub clearPics()
Dim shp As Shape

    For Each shp In Worksheets("sort").Shapes

        If shp.Type = 11 Then
                shp.Delete
        End If

    Next shp
End Sub

VBA type mismatch error when deleting pictures

2 Answers2