Dynamic Named Range - not standard

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi

I have seen several examples where OFFSET or COUNTA are used to find last cell containing either a text OR a numeric value

I want it to find ANY value. As the users may cut my sheet up in all sorts of ways.

Most use MATCH("*" for text or MATCH(1E+306 for numeric

I can't use COUNTA as the user may leave gaps.

Would like a dyanmic INDEX or method to find the last cell that is not blank ? (and using "<>""" only works for text, wont detect numbers!)

Help appreciated

ALSO...what are the codes to represent a very large number in excel 9E+ or something? Seen people using them in MAX and MIN functions to find largest and smallest items? Also use 255 and Z code mixes for matching

Thanks
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

gehusi

Board Regular
Joined
Jul 20, 2010
Messages
182
Try using

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

The large number formula looks like 1E+100 (a 1 with 100 hundred zeros - change to suit your needs).
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
I tried this before coming on MrExcel. Doesn't work as does not count blank cells. Only counts cells with value. So if there are gaps, the range doesnt include all cells.

There may be gaps and so I was thinking more along lines or ROW() within an index match, or some other elegant solution
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,399
Messages
5,831,408
Members
430,065
Latest member
ctornabe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top