Re: Last valid value in array

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Juan Pablo González (JuanPablo_at_MrExcel.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 16:46:49 -0500

Use

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

and drag the formula down.. 

-- 
Regards
Juan Pablo González
"bcmiller >" <<bcmiller.181lux@excelforum-nospam.com> wrote in message
news:bcmiller.181lux@excelforum-nospam.com...
> 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/
>

Relevant Pages

  • Re: removing early duplicates
    ... then drag fill this down the column. ... In the dropdown in B1, ... If this isn't what you want, post back with what you mean by UDF. ... Tom Ogilvy "Mike" wrote in message ...
    (microsoft.public.excel.programming)
  • Re: Dell dimension 5150 cdc dont open?
    ... CD-R? ... CD-RW? ... Burned as UDF (i.e. drag and drop). ...
    (alt.sys.pc-clone.dell)
  • Re: Dell dimension 5150 cdc dont open?
    ... CD-R? ... CD-RW? ... Burned as UDF (i.e. drag and drop). ...
    (alt.sys.pc-clone.dell)
  • Re: hash function for large strings
    ... quite a lot faster than applying array formulas. ... Here's an example UDF to do the same thing: ... Function reduce(test As String) As String ... than 255 characters in length. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Over 29 args in a UDF with double parens
    ... using the extra parenthesis will pass all references as an array to the ... else you could test against passing multiareas ... If you write a UDF 'to the max': ... > could somehow determine what cell references were passed. ...
    (microsoft.public.excel.programming)