asked on

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.

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack Barresse

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

Zack Barresse

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

MrDavidThorn

ASKER

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)?

Zack Barresse

Do you actually have data below the source data for the chart?

Zack