View Full Version : Functions in cells prevent finding last cell value in table columns

05-18-2017, 11:38 PM
I've been away from excel for awhile and am being stumped by something I'm sure I knew the answer to once?

I have a table and in each cell a function resides to produce a value (number or text) when conditions are met. The table populates top down row by row with no gaps. I'm trying to use vba or even a Index Match Rept function to retrieve the last value displayed in certain columns. The problem is (xlUp) or (xlDown) or I M R sees the functions in the column cells as values and won't find the last displayed value.

The only way out I can see, is to copy and paste the displayed values into seperate columns, then work on them. I'm sure there is another way though?


05-19-2017, 12:10 AM
Eventually googled a result which was a deja vu moment, as I have been here before!

if column range is V4:V41 Use: "=LOOKUP(2,1/(V$4:V$41<>""),V$4:V$41)"

Surprising how difficult this answer was to obtain, and not addressed by any vba examples that I could find? None that worked anyway.

05-19-2017, 07:16 AM
Function LastValue(AnyCellInColumn As Range) As Variant
Dim Cel As Range

Set Cel = Cells(Rows.Count, AnyCellInColumn.Column).End(xlUp)
Do While Cel <> "" '<--Any valid condition
Set Cel = Cel.Offset(-1)

LastValue = Cel
End Function

05-19-2017, 10:18 AM
SamT, that's a gem of a piece of code. It was a deja vu moment too, seeing your name pop up again, as you've been of great assistance in the past. Thankyou kindly for the reply.