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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top