Finding last row - non-VBA

[Jack Sheet]

Sorry for such a trivial question, but I want to define a named range without using VBA that embraces all used cells in a specified column. Unused cells between two used cells should be included in the range. Ie it should be a single continuous range starting with the first used cell in the column and ending with the last used cell in the column. And it needs to update on recalculation should a new last cell be entered since last file save.

Any help gratefully received.

 

[PaddyD]

=INDEX(Sheet1!$A$1:$A$100,MIN(IF(LEN(Sheet1!$A$1:$A$100)>0,ROW(Sheet1!$A$1:$A$100)))):INDEX(Sheet1!$A$1:$A$100,MAX(IF(LEN(Sheet1!$A$1:$A$100)>0,ROW(Sheet1!$A$1:$A$100))))

...change a1:a100 to a range of cells suitably larger than anything your data is likely to occupy - really is overkill testing all rows if you're never likely to get past 10,000 used etc...

 

[RoryA]

What is your definition of "used"? Do you need to include formula cells that may be returning "" rather than a value?

 

[Jack Sheet]

Good point Rorya. I would want to include formula cells that return "". Thanks PaddyD I shall try that out

 

[pgc01]

Hi Jack

To include formula cells that return "", both at the beginning or at the end, try:

=INDEX(Sheet1!$A:$A,MATCH(1,(Sheet1!$A$1:$A$100<>"")+ISTEXT(Sheet1!$A$1:$A$100),0)):INDEX(Sheet1!$A:$A,LOOKUP(2,1/((Sheet1!$A$1:$A$1000<>"")+ISTEXT(Sheet1!$A$1:$A$1000)),ROW(Sheet1!$A$1:$A$1000)))

 

[Jack Sheet]

I'd like to thank all y'all for your input.
I have found that the suggested solutions all work but seem to place an unacceptable burden on the overhead, by which I mean the time taken to recalculate the workbook gets irritating. So I have re-thought my whole approach, by using formulae within cells to identify the desired range, and it seems to run now quite smoothly. Anyway, I have libraried the solutions for dissection! Thanks again.

("Libraried" -- there is no noun that cannot be verbed)