Forum Discussion
Sort/filter cells with picture
Hi all,
I have an excel sheet some cells have picture others don't is there a quick way to sort or filter the items which have pictures?
Thanks in advance.
7 Replies
ok one more question Drin365
That's a lot of records and probably a lot of images.
I wonder about the source of the data.
Was it pulled from somewhere that might have the option to export additional data that might include some other field related to the image name, or anything like a flag that might identify the items/products that have images on file?
I cringe at the thought that someone sat there and hit copy and paste to line it up for each.
- It was sent to me like that from a supplier so that's the only data I have. Not sure but might have used the Excel Image Assistant to insert pics.
- I found the below VBN methods,
METHOD1
Function CellImageCheck(CellToCheck As Range) As Integer
' Return 1 if image exists in cell, 0 if not
Dim wShape As Shape
For Each wShape In ActiveSheet.Shapes
If wShape.TopLeftCell = CellToCheck Then
CellImageCheck = 1
Else
CellImageCheck = 0
End If
Next wShape
End Function
METHOD2
Sub CellImageCheck()
Dim checkRange As Range
Dim x As Shape
Dim flag As Boolean
On Error Resume Next
Set checkRange = Application.InputBox("please enter one cell that you want to check", "CellImageCheck", Selection.Address, , , , , 😎
If checkRange Is Nothing Then Exit Sub
flag = False
For Each x In ActiveSheet.Shapes
If x.TopLeftCell.Address = checkRange.Address Then
flag = True
End If
Next
If flag Then
MsgBox "Found an image!"
Else
MsgBox "No Image"
End If
End Sub
Method 1 it doesn’t work, all results are 1 irrelevant is there is or isn’t a picture unless there is a text.
Method 2 is working fine but obviously, it doesn’t make sense to use is it takes longer the checking manually.
Can someone help?
If the image fits within the cell, it might actually sort with the row Drin365
Just click in cell B2, go to the DATA tab, and select the easy sort icon for Z to A and see what happens.
You can also click undo or close the file without saving if the results are not as expected.
Again, as I mentioned, if you want to control the sort of the picture you may need to do some kind of formula if you can. Unless the row count is low enough for you to eyeball it (I would wait until after you sort so the similar images are together).
- Is the image literally embedded in the cell? Otherwise, sorting would only rearrange column A. If the images are something like a webding/wingding, you should be able to sort by that. And if you wanted to control the sort order, you could use column C to provide a numerical value to sort them in a specific order, and you could assign the # via a formula you could quickly copy and paste down. However, this could be timely though if you have a large variety of images. Just a couple ideas.
