Finding last cell with number in it

I have sheets that are filled with data that changes once per quarter. The data will start in cell E6 but will be in a different ending point. I need to look up the last cell with data by row and column. I have tried and hit a wall. here is the code I have so far.

Please Login or Register  to view this content.

Thanks all.

You can retrieve the value of the last cell using this formula.
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))
Use Ctrl+Shift+Enter while putting it in to make it an array.

Are you trying to get the page to take you to the location of the last numeric value in your range?

What I am trying to do is find the last cell with a number because that range corresponds to a chart on the sheet. The problem comes in because there is a lot more other information on the page in addition to the data I need and the chart. The data starts in Cell E2 but it could end down and to the right at any number of locations

Yes, we are dealing with buildings so when we put a new Building ID in it changes the data. All buildings being different size the data table is rarely the same. So it fills the cells it needs to and every other cell filled with "#NA". I can't go back and change that portion so just trying to work around what is already there

"#NA" is just a filler, so I do not want to find that.
The data I am trying to find eventually goes into a chart on the same worksheet. When we put in new data the chart changes but tries to keep the old data and match it with the new.
So my goal to get started with a macro that makes the new data seamlessly enter the new chart is by finding the first and last cell with the important data (numbers instead of #NA) and then select that range of data.
So basically find the top left and bottom right corners of the box and then connect the dots giving me the entire box of data.
With this in mind I need the cell that is the last in the box that has a number in it. Basically the last "hit" Battleship haha

hopefully this helps

Please read up on cross posting

http://www.mrexcel.com/forum/excel-q...ll-number.html

Arkadi!!! thanks for your help so far. You are saving me. The values we need to be fluid are $D$40 and $G$5:$AK$40.

$D$40 = Value in E1 on active sheet
$G$5 = Value in E2 on active sheet
$AK$40 = Value in E3 on active sheet

And we can make this apply to the chart in the active sheet as we loop through all our sheets (we already have the loop but need this macro to engage the chart on the active sheet). here is what happened when I recorded a macro of choosing the cells you found.

Please Login or Register  to view this content.

here is the workbook!

Test Sheet (Updated Jonathan's Version 6.19.15).xlsm