I would like to request help. I have on my hands a lot of excel sheets that has a picture in every row. When I copy and paste rows, the pictures are copied into the pasted rows as well. However, when I want to delete rows, the picture does not get deleted. Instead, it "hides" behind the picture of the next row.
我想请求帮助。我手上有很多excel表,每行都有一张图片。复制和粘贴行时,图片也会复制到粘贴的行中。但是,当我想删除行时,图片不会被删除。相反,它“隐藏”在下一行的图片后面。
Also, when I sort/filter the list, the pictures does not seem to follow. The picture stays in the same place, but all the other values are sorted/filtered. My job is to delete and randomise some of the entries, so you can imagine my woes with all the stubborn pictures that just refuse to belong in its own row. Currently, I am able to filter, highlight the cells I want to delete, unfilter (so that the pictures match the rows), delete the pictures and then delete the rows manually. As for randomisation, I am stuck as using the "RAND()" function in excel requires sorting of rows, which will mess up the pictures.
此外,当我对列表进行排序/过滤时,图片似乎没有跟随。图片保持在同一位置,但所有其他值都被排序/过滤。我的工作是删除和随机化一些条目,所以你可以想象我的困境与所有顽固的图片,只是拒绝属于自己的行。目前,我能够过滤,突出显示我要删除的单元格,不过滤(使图片与行匹配),删除图片,然后手动删除行。至于随机化,我被困在使用excel中的“RAND()”函数需要对行进行排序,这会弄乱图片。
I would greatly appreciate if somebody could provide a solution on how to work around this problem. This forum thread http://www.ozgrid.com/forum/showthread.php?t=85597 also describes what I am experiencing. However, when I tried the VBA code, it says "run-time error 13" and I don't know what that means. Trying that code was my very first try with VBAs and macros, but I am pretty confident that I followed instructions on how to add and run them properly. It debugged at the sentence For Each sh In Shapes.
如果有人能提供解决这个问题的解决方案,我将不胜感激。这个论坛帖子http://www.ozgrid.com/forum/showthread.php?t=85597也描述了我的体验。但是,当我尝试使用VBA代码时,它会显示“运行时错误13”,我不知道这意味着什么。尝试使用这个代码是我第一次尝试使用VBA和宏,但我非常有信心我遵循了如何正确添加和运行它们的说明。它调整了句子For Each sh In Shapes。
Some more details: My picture properties are set as "Move and size with cells". There is also only one picture per row, not counting the heading. If you guys require me to send the excel file, I am unable to do so as it contains private information. However, I am able to change the data. Hope that will work!
更多细节:我的图片属性设置为“移动并使用单元格大小”。每行只有一张图片,不包括标题。如果你们要我发送excel文件,我无法这样做,因为它包含私人信息。但是,我能够更改数据。希望能工作!
1 个解决方案
#1
0
Pictures float above cells:
图片漂浮在细胞上方:
The upper left-hand corner is in cell B2............we can use this to decide which pictures to delete before deleting the row. So if we want to delete row #2 and its associated pictures:
左上角在单元格B2 ............我们可以使用它来决定在删除行之前删除哪些图片。因此,如果我们想要删除第2行及其相关图片:
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 delete row#2
然后删除第2行
#1
0
Pictures float above cells:
图片漂浮在细胞上方:
The upper left-hand corner is in cell B2............we can use this to decide which pictures to delete before deleting the row. So if we want to delete row #2 and its associated pictures:
左上角在单元格B2 ............我们可以使用它来决定在删除行之前删除哪些图片。因此,如果我们想要删除第2行及其相关图片:
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 delete row#2
然后删除第2行