On Sunday, April 7, 2013 10:38:51 PM UTC-7, GS wrote:
Ok! Let me know if you need help with the dynamic named range...



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

I struggled a bit but got an example from excel google where you use
=OFFSET(...,etc...,0,)) which is basiclly range("C9:C32") and a
counta of the same range. And the name BreakdownList.

It worked with your code. Is that a true dynamic named range? The
way the sheet is set up those 24 rows are pretty much the extent of
range's range.

Doesn't have room to grow to 75 rows say...

Howard

Well no, that's not the correct way to make a dynamic range because it
includes blanks, which you do not want to process. Here's how I would
define it...

Select C8;
In the Namebox (left of the FormulaBar) type...
"summary!BrkDn_Hdr"
without the quotes;

Open the Name Manager dialog and edit "BreakdownList" RefersTo as
follows...

=OFFSET(BrkDn_Hdr,1,0,COUNTA($C$9:$C$32),1)

...making sure that "Summary" is specified in the Scope field. This will
show the address when typed in the Immediate Window of the VBE...

?range("BreakdownList").Address

...and press Enter to see "$C$9:$C$12", which is the area where the 4
entries under BREAKDOWN reside.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion