within a function I need to reference the value of last non empty cell.
Usually I have contiguous range of cells, but sometimes there might
be empty cells.
Basically I need an equivalent of
LOOKUP(9.99999999999999+307,D:D)
say for column D

Thanks in advance for any help,
/mark

Hi Mark,

Try

Dim Rng as Range

Set Rng = Cells(Rows.Count, "D").End(xlUp)


---
Regards,
Norman



"Mark Dvorkin" wrote in message
...
within a function I need to reference the value of last non empty cell.
Usually I have contiguous range of cells, but sometimes there might
be empty cells.
Basically I need an equivalent of
LOOKUP(9.99999999999999+307,D:D)
say for column D

Thanks in advance for any help,
/mark

Hi Mark,

I missed that you wanted the value rather than the range!

MyValue = Cells(Rows.Count, "D").End(xlUp).Value


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Mark,

Try

Dim Rng as Range

Set Rng = Cells(Rows.Count, "D").End(xlUp)


---
Regards,
Norman



"Mark Dvorkin" wrote in message
...
within a function I need to reference the value of last non empty cell.
Usually I have contiguous range of cells, but sometimes there might
be empty cells.
Basically I need an equivalent of
LOOKUP(9.99999999999999+307,D:D)
say for column D

Thanks in advance for any help,
/mark

Hi Mark,

Norman's solution is the standard fare but to follow through on your
attempt, you could go:


Sub mm()
x = Application.Lookup(9.999999E+30, [d:d])
msgbox x
End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=506981