Last valid value in array
 

Hi,

I have written a UDF to search through a range to find the last vali
value eg:

a b c d e
1 1 5 5
2 6
3 1 7 8

In these examples 5, 6 and 8 would be the last valid enties

Basically I transfer the range to an array and search from the end bac
until I find a value. The problem is that the values don't update whe
data is changed. I had thought about using application.volatile bu
this is dangerous and has caused me problems in the past.

Is there and easier way to do this either by formula or better designe
UDF???

Cheers,

B

--
Message posted from http://www.ExcelForum.com

Last valid value in array
 

Use

=INDEX(1:1, MATCH(9.999999E+307,1:1))

and drag the formula down..

--
Regards

Juan Pablo González

"bcmiller " wrote in message
...
Hi,

I have written a UDF to search through a range to find the last valid
value eg:

a b c d e
1 1 5 5
2 6
3 1 7 8

In these examples 5, 6 and 8 would be the last valid enties

Basically I transfer the range to an array and search from the end back
until I find a value. The problem is that the values don't update when
data is changed. I had thought about using application.volatile but
this is dangerous and has caused me problems in the past.

Is there and easier way to do this either by formula or better designed
UDF???

Cheers,

BC


---
Message posted from http://www.ExcelForum.com/

Last valid value in array
 

It works a treat. Many thanks

--
Message posted from http://www.ExcelForum.com