Finding the first empty cell in a list

[Todd Huttenstine]

I have a list of words in cells A1:A20. What is the formula I would use to
find the 1st empty cell in that range? Lets say A1:A7 all contain a word
but then the list ends. I need for the formula to come up with the answer 8
because the 8th cell is the first empty cell.


Thanx

Todd

 

[Pieter Kuyck]

Todd
With an array formula

=MAX(IF(A1:A20<>0,ROW(A1:A20)))+1
entered with CTRL+SHIFT+ENTER

Pieter


| I have a list of words in cells A1:A20. What is the formula I would use to
| find the 1st empty cell in that range? Lets say A1:A7 all contain a word
| but then the list ends. I need for the formula to come up with the answer 8
| because the 8th cell is the first empty cell.
|
|
| Thanx
|
| Todd
|
|

 

[Pieter Kuyck]

Too fast send,

The row of the first empty cell, an array formula

=MIN(IF(A1:A20="",ROW(A1:A20)))
entered with CTRL+SHIFT+ENTER

The row of the cell after the last entry
=MAX(IF(A1:A20<>"",ROW(A1:A20)))+1
entered with CTRL+SHIFT+ENTER

Pieter

| Todd
| With an array formula
|
| =MAX(IF(A1:A20<>0,ROW(A1:A20)))+1
| entered with CTRL+SHIFT+ENTER
|
| Pieter
|
|
| | I have a list of words in cells A1:A20. What is the formula I would use to
| | find the 1st empty cell in that range? Lets say A1:A7 all contain a word
| | but then the list ends. I need for the formula to come up with the answer 8
| | because the 8th cell is the first empty cell.
| |
| |
| | Thanx
| |
| | Todd
| |
| |
|
|

 

[acw]

Todd

Another way:
=COUNTA(A1:A20)+1

But this does assume that the range A8 - A20 is blank.

Tony

 

[David]

Todd,
For any single column range named MyRange:
=MIN(IF(MyRange="",ROW(MyRange),"")) Ctrl+Shift+Enter
will return the row number of the first empty cell down
the page.

David

 

[Norman Harker]

Hi Todd!

Hunting for "" will probably suffice for most purposes but if you want
to check for blank cells and you define cells that return "" as *not*
being blank then you need a formula such as:

=IF(ISNA(MATCH(1,ISBLANK(MyData)*1,0)),COUNTA(MyData)+1,MATCH(1,ISBLAN
K(MyData)*1,0))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.

If entered correctly it shows in the formula bar as:
{=IF(ISNA(MATCH(1,ISBLANK(MyData)*1,0)),COUNTA(MyData)+1,MATCH(1,ISBLA
NK(MyData)*1,0))}

MyData is a named range of cells.

=MATCH(1,ISBLANK(MyData)*1,0)
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.
Returns the first exact cell in the MyData range which is blank.
Note that if there is no match, this will return #N/A

So:
=ISNA(MATCH(1,ISBLANK(MyData)*1,0))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.
Returns TRUE if there is no MATCH

What you do if there isn't a blank is up to you. But COUNTA(MyData)+1
will return the cell after the MyData range.

We can then use the standard form:
=IF(FormulaIsError,ErrorFormula,Formula)

To get the suggested formula:
=IF(ISNA(MATCH(1,ISBLANK(MyData)*1,0)),COUNTA(MyData)+1,MATCH(1,ISBLAN
K(MyData)*1,0))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Monday: Central African Republic (National Day of
Prayer); El Salvador (Balance Day); Guatemala (Army Day); Israel (Rosh
Hodesh Tammuz); Sudan (National Salvation Revolution Day); Ukraine
(Constitution Day); Zaire (Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.