Formula needed for first blank row

  • Thread starter Thread starter Russ
  • Start date Start date
R

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
 
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
 
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.
 
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
 
Gord, Excel will probably fix it anyway, but the last parenthesis fell off of
your first formula :-)
 
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


.
Click to expand...
 
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
 
Back
Top