Finding the first empty cell in a list

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

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
 
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
|
|
 
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
| |
| |
|
|
 
Todd

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

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

Tony
 
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
 
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.
 
Back
Top