Worksheet Function to Find First Cell With Contents in a Ran

[MattHyatt]

I have a range A1:A100, sometimes with five rows of data, and sometimes with 95 rows of data. I know how to find the first empty row in the range using a macro (thanks to MrExcel), but I would like to get the same information with a worksheet function. Any suggestions?

 

[phantom1975]

WELCOME TO THE BOARD!

You can try something like:

=VLOOKUP("",A1:A100,1,false)

 

[MattHyatt]

If A1:A3 contains 1, 2, and 3, respectively, A4 is blank, and B1 contains function:

=VLOOKUP("",A1:A4,1,FALSE)

Excel returns #N/A in B1.

I also tried MATCH using "" with similar results.

 

[Mark W.]

=MATCH(9.99999999999999E+307,A:A) returns the last row number.

 

[PaddyD]

http://216.92.17.166/board/viewtopic.php?topic=15391&forum=2

 

[Aladin Akyurek]

On 2002-10-14 16:23, MattHyatt wrote:
I have a range A1:A100, sometimes with five rows of data, and sometimes with 95 rows of data. I know how to find the first empty row in the range using a macro (thanks to MrExcel), but I would like to get the same information with a worksheet function. Any suggestions?

Is A1:A100 the range of interest, that is, Is this range fixed?

 

[MattHyatt]

Well, THAT'S interesting. It does work ... but WHY?

 

[PaddyD]

which one?

 

[Aladin Akyurek]

On 2002-10-14 17:31, MattHyatt wrote:
Well, THAT'S interesting. It does work ... but WHY?

There are a number of threads in which the big number as lookup value for MATCH is discussed (if that's what you're asking). One such a thread is

http://makeashorterlink.com/?J18B24961
This message was edited by Aladin Akyurek on 2002-10-14 18:00

 

[Mark W.]

Magic!! 9.999999999999999E+307 is the largest value that Excel can store. =MATCH(9.999999999999999E+307,A:A) "looks" for the largest value that is less than or equal to 9.99999999999999E+307, and is expecting the list to be in ascending order. Since it never finds a value that large it goes all the way to the end of the list (the last value encountered) and returns it's row number.