Get first blank cell

  • Thread starter Thread starter Andrew Clark
  • Start date Start date
A

Andrew Clark

Hello,

I would like to get the number of the row with a blank in a certain column.
I was trying something like this:

{=IF(ISBLANK(<range>),ROW(<range>),0)}

but that's not working. Can I do this without resorting to a macro?

Thanks,
Andrew
 
One way:

=MIN(IF(A1:A200="",ROW(A1:A200)))

Ctrl-shift-enter. It's an array formula.
 
7 characters less to type also array entered <g>

=MATCH(1,--(A1:A200=""),0)


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Peo Sjoblom said:
7 characters less to type also array entered <g>

=MATCH(1,--(A1:A200=""),0)
Click to expand...
....

If brevity is the goal,

=MATCH(2,1/(A1:A200=""))

And this should be more recalc efficient too.
 
The double unary is used to coerce the results of the test for blanks to a
number. This is because the test for blanks will return True, False, or an
error. Peo's formula is trying to find the first value of 1 and so he needs
to change the Trues to 1. The -- coerces False to a 0, and True to a 1, Thus
by looking for the first value of 1, he finds the first blank.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top