VBA Code, Select the last non-blank(visible) cell with a value in Column C

Hello All,

I am a novice to VBA but well verse in writing formulas.

I would like a VBA code to go to Sheet "Slide 1 Chart 1", select the LAST CELL in Column C that has a populated(visible) cell value, copy that cell, and then paste(value only) into that same cell.

Each cell in Column C had/has a formula that pulls in "Values" if the formula is true. I run a report each day were the last cell with a value in Column C is dynamic by a formula. The cells above it are static(No formula). On the following day of running that report the last cell with a value needs to become static. Thus "Select", "Copy", "Paste(Values only)".

LastColumn;Row;Cell does not work as it still see's the formula in the visibly blank cell and considers it the lastrow. It should ignore the formulas and only look at if a value(not data) is populated.

Please help me with this!
above.PNG

lastcell.PNG

below.PNG

Thank you,

Sub TEST()

'Find the last used row in column C.  We use this row number to limit the size of our array formula
LR = Cells(Rows.Count, 3).End(xlUp).Row

'Enter an array formula in D1 to find the last cell with text in Column C

'The Formula creates an array listing the row number of each cell with text.  The max returns the row number of the last cell

Range("D1").FormulaArray = "=MAX(IF(LEN(R1C3:R" & LR & "C3)>0,ROW(R1C3:R" & LR & "C3),0))"

'Copy Paste Valuezs
Cells([D1], 3) = Cells([D1], 3)

'Clear D1
[D1] = ""
End Sub