FIND LAST ROW: 3 ways if column has numbers or text...

[itr674]

I copied this from post http://www.mrexcel.com/board2/viewtopic.php?t=28663&highlight=bignum in hopes of making it faster for folks looking for this info--took me an hour to find it, but I'm slow... Hope I have it correct!

If myRange is of numeric type...

=MATCH(9.99999999999999E+307,myRange)

If myRange is of "text" type...

=MATCH(REPT("z",90),myRange) ** 255 appears to work for 90 **

If myRange is of mixed type...

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

Note that you can replace 9.99999999999999E+307 with BigNum in the above formulas by defining BigNum as follows...

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Aladin (Aladin Akyurek)

You could also define BigStr for text columns by:

(1.) Activate Insert|Name|Define.
(2.) Enter BigStr in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

REPT("z",255)

 

[Aladin Akyurek]

That MAX formula for a range of presumably mixed type (text and/or numeric) needs revision...

Let column B of Sheet1 from B2 on be the range of interest.

In A1 enter:

=MATCH(REPT("z",255),Sheet1!B2:B65536)

In A2 enter:

=MATCH(9.99999999999999E+307,Sheet1!B2:B65536)

In A3 enter:

=CHOOSE(COUNT(A1:A2)+1,#N/A,SUMIF(A1:A2,"<>#N/A"),MAX(A1:A2))

A3 is the desired result.