Formula needed for first blank row

[Russ]

Hello,

Does anyone know if there is a formula equivalent to the
VBA statement

"Range("A" & rows.Count).End(xlUp).row + 1"

which finds the first blank row at the end of the used
range (here, in column A) ?

If so, this would be really helpful to know.

Thanks,

Russ

 

[Frank Kabel]

Hi Russ
try
=OFFSET($A$1,COUNTA($A:$A)-1,0)

HTH
Frank

 

[Ken Wright]

Hi Frank - Need to be careful there, as that depends on whether there are any
blank rows within the used range, as the Op didn't preclude that from his
request. He was specific about the first blank after the usedrange, in which
case he may be better off with something like:-

=MATCH(REPT("z",255),A:A)+1 for just the row number
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A)+1) for the cell

or

=MATCH(9.99999999999999E+307,A:A)+1 for just the row number
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A)+1) for the cell

or

=MAX((A1:A65535<>"")*ROW(A1:A65535))+1 array entered for just the row number
=INDEX(A1:A65535,MAX((A1:A65535<>"")*ROW(A1:A65535))+1) array entered for the
cell

 

[Ken Wright]

Would have helped to clarify really wouldn't it

First set of formulas was if data was text, second if data was numeric, or 3rd
didn't matter either way.

 

[Gord Dibben]

Russ

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1
to return the address of last numeric cell in column A

=LOOKUP(9.99999999999999E+307,A:A)
to return the last numeric value in Column A

And just for helluvit....

=LOOKUP(REPT("z",255),A:A)
to return the last non-numeric value in Column A

=ADDRESS(MATCH(REPT("z",255),A:A),1)
to return the address of last non-numeric cell in column A

Gord Dibben Excel MVP

 

[Ken Wright]

Gord, Excel will probably fix it anyway, but the last parenthesis fell off of
your first formula

 

[Russ]

Thanks Frank! It works like a charm!

Russ

-----Original Message-----
Hi Russ
try
=OFFSET($A$1,COUNTA($A:$A)-1,0)

HTH
Frank



.

 

[Russ]

Thanks guys! Much obliged!

-- Russ

-----Original Message-----
Gord, Excel will probably fix it anyway, but the last parenthesis fell off of
your first formula

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004


.

 

[Frank Kabel]

Hi Ken
you're right, my assumption was 'no blank rows' in between. But should
have mentioned it. And yes clarification would have been helpful
Regards
Frank

 

[Ken Wright]

LOL