Find last row that is empty, insert picture (code not recognising cells with images in already)

[rbsam]

So my code currently works like this:

- User clicks insert button
- Button launches a file browser
- User selects file (an image)
- The code assigns a variable (filename) to the file path
- It then inserts a rectangle shape on the last empty row
- The image fill is made to be the image the user selected via the file path

Works without any issues, apart from the following...
(where I say image, it is actually a rectangle shape with an image fill)

When there are no images in the column, and the user hits the button for the first time, it inserts an image into the correct cell (the first one that is empty). However when the user goes to select the 2nd image, it puts it in the same cell as the first.

I've worked out that it isn't recognising a shape/image being in a cell as containing value.
If I entered some text into the first cell, then clicked the insert button, it will put the image into the cell below, as it should.

Is there a way to ensure the cell recognises that when a shape is inside it then the cell has value and for the code to look for the next cell below?

Dim LastRow As Long
LastRow_num = Cells(Rows.Count, 3).End(xlUp).Row
LastRow_num = LastRow_num + 1
EmptyRow = "C" & LastRow_num


Dim filename As String
filename = Application.GetOpenFilename


Dim clLeft As Double
Dim clTop As Double
Dim clWidth As Double
Dim clHeight As Double


Dim cl As Range
Dim shpRec As Shape


Set cl = Range(EmptyRow)


clLeft = cl.Left
clTop = cl.Top
clHeight = cl.Height
clWidth = cl.Width


Set shpRec = ActiveSheet.Shapes.AddShape(msoShapeRectangle, clLeft, clTop, 370, 240)




    With shpRec.Fill
        .Visible = msoTrue
        .UserPicture (filename)
        .TextureTile = msoFalse
    End With

(the reason the LastRow_num has a +1 is because the code finds the row with the last value, whereas I need it to point to the last EMPTY row)

Any help would be much appreciated!

 

[rbsam]

Not sure how to delete this post but I had someone over at StackOverflow reply with the following code which resolved the issue

[COLOR=#000000][FONT=Menlo]Function NextEmptyRowForShapes(ByVal ws As Worksheet, ByVal col As Long) As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Dim lastRow As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    With ws[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Dim s As Shape[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        For Each s In .Shapes[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            If Not Intersect(s.BottomRightCell, .Columns(col)) Is Nothing Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]                If s.BottomRightCell.Row > lastRow Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]                    lastRow = s.BottomRightCell.Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]                End If[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            End If[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Next s[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    End With[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    NextEmptyRowForShapes = lastRow + 1[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]End Function[/FONT][/COLOR]

And then calling it like this

[COLOR=#000000][FONT=Menlo]LastRow_num = NextEmptyRowForShapes(ActiveSheet, 3)[/FONT][/COLOR]

Thought I'd leave that here in case someone has the same problem and finds this thread