Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel to find first empty cells address in a given range

Posted on 2012-04-11
5
Medium Priority
?
189 Views
Last Modified: 2012-04-17
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.
0
Comment
Question by:MrDavidThorn
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 37833627
Hi,

One option

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

Kris
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37833661
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37833665
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
 

Author Comment

by:MrDavidThorn
ID: 37833748
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37834963
Do you actually have data below the source data for the chart?

Zack
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question