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?

 

[Aladin Akyurek]

On 2002-10-14 17:56, MattHyatt wrote:
Well, I thought I was home-free, but I'm not. It works fine with numbers, but when I went back to my "real world scenario", I found that it doesn't work when the array is full of text.

There must be some way to get MATCH to convert the text to numbers. Maybe MID or LEN or something. Going back to do some more research. Additional comments welcome.

Thanks!

=MATCH(REPT("z",90),A:A)

will give the row number if col A is of text type.

If col A is of mixed data type (text & numbers), use:

=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",90),A:A))