How do I automatically delete snapshots when deleting rows?

I would like to ask for help. I have many excellent sheets in my hands that have a photograph in each row. When I copy and paste the lines, the images are copied to the inserted lines as well. However, when I want to delete lines, the image is not deleted. Instead, he "hides" behind the picture of the next line.

Also, when I sort / filter the list, the images don't seem to follow. The image stays in one place, but all other values ​​are sorted / filtered. My job is to delete and randomize some entries so you can imagine my problems with all the stubborn photos that just refuse to belong in their own row. Currently, I can filter, highlight the cells I want to delete, unfilter (to make the images match the rows), remove the snapshots, and then manually delete the rows. Regarding the randomization, I am stuck as using the "RAND ()" function in excel requires sorting the rows, which will ruin the photos.

I would really appreciate it if someone could provide a solution on how to get around this issue. This forum thread also describes what I'm experiencing. However, when I tried the VBA code, it says "runtime error 13" and I don't know what that means. Trying this code was my first try with VBA and macros, but I'm pretty sure I'm following the instructions on how to add and run them correctly. It is debugged in a sentence For Each sh In Shapes


Additional information: My image properties are set to "Move and size with cells". There is also only one image per line, not counting the title. If you guys require me to submit an excel file, I cannot do that as it contains personal information. However, I can change the data. Hope it works!


source to share

1 answer

Images float over cells:

enter image description here

top left - hand corner is in cell B2 ............ we can use this to decide which shots to delete before deleting the row. So if we want to delete line # 2 and its associated photos:

Sub dural()
    Dim shp As Shape, rng As Range
    Dim WhichRow As Long

    For Each shp In ActiveSheet.Shapes
        Set rng = shp.TopLeftCell
        WhichRow = rng.Row
        If WhichRow = 2 Then shp.Delete
    Next shp
End Sub


Then remove line # 2



All Articles