VBA Commands to Delete All Pictures Above a Certain Row

[MikeG]

I received some nice code from Alex Blakenburg that allows me to delete all shapes above row 5 in my workbook. I now also want to delete all pictures above row 5 as well. So I took the original code for shapes and tried to do the same for pictures using a variable I named pic. My code is shown below.

However, the code creates a Type Mismatch error when it comes to the line - For Each pic In ws.Pictures

I would appreciate any help in correcting the code.

Thanks

Private Sub Delete_Shapes_and_Pictures()


Dim ws As Worksheet
Dim shp As Shape
Dim pic As Picture


For Each ws In ThisWorkbook.Worksheets
 
    For Each shp In ws.Shapes
        If shp.Type = msoAutoShape And shp.BottomRightCell.Row < 5 Then shp.Delete
    Next shp

    For Each pic In ws.Pictures
        If pic.Type = msoPicture And pic.BottomRightCell.Row < 5 Then pic.Delete
    Next pic

Next ws
    

End Sub

 

[rollis13]

Change the 'pic' part of your macro to this:

'...
For Each pic In ws.Pictures
    If pic.BottomRightCell.Row < 5 Then pic.Delete
Next pic
'...

 

[MikeG]

Change the 'pic' part of your macro to this:

'...
For Each pic In ws.Pictures
    If pic.BottomRightCell.Row < 5 Then pic.Delete
Next pic
'...

Thanks! That works great.

I do have a related question that I wonder I can get help on: I have grouped two shapes together and would also like them to be subject to deletion if above row 5.

When I run the corrected macro above this group is not deleted, I assume because the group is no longer a shape object. Could someone give the additional code required to delete the groups as well?

And also, out if interest, if I were to group a picture with a shape, would the same code delete that as well, or is their a distinction between a group that consists of only shapes versus one that is a mix of shapes and pictures?

Thanks again.

 

[Domenic]

When grouping two shapes together, the grouped shape becomes part of the Shapes collection. You can test for it using If shp.Type = msoGroup . . .. The same thing applies when you group a shape and a picture.

 

[MikeG]

When grouping two shapes together, the grouped shape becomes part of the Shapes collection. You can test for it using If shp.Type = msoGroup . . .. The same thing applies when you group a shape and a picture.

Thanks!

 

[Domenic]

You're very welcome, cheers!