0

I have this Column A and I have these 2 images in Rows 1 and 4: here is the before

I want a loop to go through column A and if the cell is empty then copy the cell above with the image in it (the image is locked inside the cell and moves/resizes/filtered with it, like this

this should be the end result

Thank you all in advance.

Tried this for non-images and it works (easier)

Sub IfEmpty_CopyAboveCell()

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = ActiveCell.Row To LastRow

    Cells(i, ActiveCell.Column).Select

    If Cells(i, ActiveCell.Column) = "" Then
        Cells(i, ActiveCell.Column) = Cells(i - 1, ActiveCell.Column)
    End If

Next i

End Sub

With images invloved it becomes trickier (didn't work yet):

Sub IfEmptyImages_CopyAboveCell()

Dim i As Integer
Dim LastRow As Integer
Application.CopyObjectsWithCells = True

LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = ActiveCell.Row To LastRow

    Cells(i, ActiveCell.Column).Select

    If CellImageCheck(Cells(i, ActiveCell.Column)) = 0 Then
        Cells(i - 1, ActiveCell.Column).Copy
        Selection.offset(1, 0).Paste
        Cells(i, ActiveCell.Column).Paste
        'Cells(i, ActiveCell.Column) = Cells(i - 1, ActiveCell.Column)
    End If

Next i

End Sub

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
4
  • "If the image is inside it" - to be clear, the image is just formatted to appear within the cell, correct?
    – BruceWayne
    Jun 11, 2019 at 14:52
  • yes, thank you for this, I have also edited my original post to be more clear
    – AG9
    Jun 11, 2019 at 14:59
  • What have you tried so far? Jun 11, 2019 at 15:37
  • I edited my original post again, i succeeded in the second part of my request (copying the cell with the image, i used "Range.FillDown", worked great). I just need to know how do I check if a cell contains an image, I saw that there's an address to each cell and an address to each Image and I can run a loop to check it, I will try that, but I wish there was an easier way
    – AG9
    Jun 12, 2019 at 7:40

0

You must log in to answer this question.

Browse other questions tagged .