Excel to find first empty cells address in a given range

Hi Experts

I have a range in Excel with a data range("A1:A100") , I need to find the first cell in the range that is empty and return its address, does anyone know the formula to find this?

Dave.
MrDavidThornAsked:
Who is Participating?
 
krishnakrkcCommented:
Hi,

One option

=CELL("address",INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100="",0,0),0)))

Kris
0
 
Zack BarresseCEOCommented:
Hello Dave,

You could use a formula like this...

=MIN(IF(LEN(OFFSET(A1,0,0,MATCH("*",A:A,-1)-1,1))=0,ROW(OFFSET(A1,0,0,MATCH("*",A:A,-1)-1,1))))

Open in new window


...which is an array-entered formula, entered with Ctrl + Shift + Enter, instead of just Enter.  This will give you an error if no answer is returnable.

Another formula, array-entered as well, could be...

=MATCH(TRUE,INDEX(A1:A100="",0),0)

Open in new window


Regards,
Zack Barresse
0
 
Zack BarresseCEOCommented:
I should mention the first formula I provided will error out if you have formulas returning a null value, while the second one will not.

Zack
0
 
MrDavidThornAuthor Commented:
Brilliant thanks Kris/Zack

The end result of what I am actually trying to do is dyamically update a chart series I.e

=SERIES(BollingerBands!$A$6,,BollingerBands!$A$6:$A$500,3)

I know have the address of the last used cell thanks to your logic, how do I apply that actuall last cell address to the series

something like
=SERIES(BollingerBands!$A$6,,BollingerBands!$A$6:CELL("contents",A4),3)?
0
 
Zack BarresseCEOCommented:
Do you actually have data below the source data for the chart?

Zack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.