Automatically change properties of ALL pictures in an Excel file

[dalbright]

I have a user that has an excel 2007 file that was exported from a piece of scientific software. There are several columns of data and one of the columns includes a picture of the scientific compound. They may have several hundred compounds that are downloaded into the spreadsheet but they may only want to keep say 50 of them. They can filter the data based on specific criteria and delete the rows they do not want to keep. However, at the very end of the spreadsheet there is one row that has each of the several hundred pictures stacked ontop of each other. Unless you select each picture and change the property "Object Positioning" to "Move and size with cells" one by one then you have to delete the objects one by one. Is there a way to change this property, either as an Excel default or through a macro? Appreciate the help in advance!

 

[Andrew Poulsom]

Welcome to MrExcel.

Here's a short macro:

Sub Test()
    Dim Pic As Picture
    For Each Pic In ActiveSheet.Pictures
        Pic.Placement = xlMoveAndSize
    Next Pic
End Sub

 

[VoG]

Try with a macro

Sub stest()
Dim oPic As Picture
For Each oPic In ActiveSheet.Pictures
    oPic.Placement = xlMoveAndSize
Next oPic
End Sub

 

[dalbright]

Awesome! Thanks so much for the quick response! I tested it and it worked!