Code Count the items in a named range
 

"Claus Busch" wrote in message Hi Howard,

Am Thu, 28 Dec 2017 10:24:41 -0800 (PST) schrieb L. Howard:

I have a dynamic range named Cable_1 that uses an OFFSET formula in the
refers to box. =OFFSET('Cable Lists'!$C$2,0,0,COUNTA('Cable
Lists'!$C:$C),1)

I have used various methods of setting to range and such, but cannot get
the occupied rows count.

i.e. Range("Cable_1").Rows.Count

Howard,
That ought to return a range C2:C(1+#) where # is the count of all non empty
cells *anywhere* in the column, ie the result of Counta. Eg if only C2 & C3
are non empty it will return the range C2:C3. If it returns a Range should
be no problem to read its rows or cells count.

It would fail if Counta returns zero, or if a worksheet level name and the
sheet is not active (unless the sheet-name is included in the address).

You presumably want to ensure C1 is always blank, if not sure change C:C to
C2:Cx where x is safely larger than will ever be a used cell. Alternatively
could include negative Counta of all non-blank cells above the top cell, in
this just simply subtract 1 if C1 is non-blank (various ways).

At a glance of the rest of the thread I didn't follow what you're actually
doing, though I didn't read it all!

Claus,
your range name is not really dynamic. It includes all cells with the
formula and not only the cells with a value < ""

It looks like a regular DNR that will dynamically size according to the
result of Counta, typically for the purpose of extending the range as new
data is added at the bottom, or am I missing something?

Peter T

Code Count the items in a named range
 

"Claus Busch" wrote in message Hi Howard,

Am Thu, 28 Dec 2017 10:24:41 -0800 (PST) schrieb L. Howard:

I have a dynamic range named Cable_1 that uses an OFFSET formula in the
refers to box. =OFFSET('Cable Lists'!$C$2,0,0,COUNTA('Cable
Lists'!$C:$C),1)

I have used various methods of setting to range and such, but cannot get
the occupied rows count.

i.e. Range("Cable_1").Rows.Count

Howard,
That ought to return a range C2:C(1+#) where # is the count of all non empty
cells *anywhere* in the column, ie the result of Counta. Eg if only C2 & C3
are non empty it will return the range C2:C3. If it returns a Range should be
no problem to read its rows or cells count.

It would fail if Counta returns zero, or if a worksheet level name and the
sheet is not active (unless the sheet-name is included in the address).

You presumably want to ensure C1 is always blank, if not sure change C:C to
C2:Cx where x is safely larger than will ever be a used cell. Alternatively
could include negative Counta of all non-blank cells above the top cell, in
this just simply subtract 1 if C1 is non-blank (various ways).

At a glance of the rest of the thread I didn't follow what you're actually
doing, though I didn't read it all!

Claus,
your range name is not really dynamic. It includes all cells with the
formula and not only the cells with a value < ""

It looks like a regular DNR that will dynamically size according to the
result of Counta, typically for the purpose of extending the range as new
data is added at the bottom, or am I missing something?

Peter T

Hi Peter,
The original project is an attempt to use the Contextures Hide Used Items
implementation of DV lists. Howard provides a link to his file; -its dynamic
named ranges are not defined as the Contexture example, but its lists and their
formulas follow the example. I rebuilt the file with the dynamic range names
defined same as the example. Both files are linked to in various replies if you
want to check them out...

--
Garry

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

Code Count the items in a named range
 

"GS" wrote in message

At a glance of the rest of the thread I didn't follow what you're
actually doing, though I didn't read it all!


Hi Peter,
The original project is an attempt to use the Contextures Hide Used Items
implementation of DV lists. Howard provides a link to his file; -its
dynamic named ranges are not defined as the Contexture example, but its
lists and their formulas follow the example. I rebuilt the file with the
dynamic range names defined same as the example. Both files are linked to
in various replies if you want to check them out...

Ah, thanks, I guess all that is all after where I gave up ;)

Still not sure why the original formula failed to produce a range but sounds
it's all in hand now.

Peter T

Code Count the items in a named range
 

"GS" wrote in message

At a glance of the rest of the thread I didn't follow what you're actually
doing, though I didn't read it all!


Hi Peter,
The original project is an attempt to use the Contextures Hide Used Items
implementation of DV lists. Howard provides a link to his file; -its
dynamic named ranges are not defined as the Contexture example, but its
lists and their formulas follow the example. I rebuilt the file with the
dynamic range names defined same as the example. Both files are linked to
in various replies if you want to check them out...

Ah, thanks, I guess all that is all after where I gave up ;)

Still not sure why the original formula failed to produce a range but sounds
it's all in hand now.

Peter T

Actually, Howard's original formula produced the dynamic ranges in the usual
fashion as opposed to how Contextures did it. That resulted problems with the
other formulas Howard wanted for counting unused list items. The Contextures
examples are excellent IMO!

FWIW:
As you know, I use the Farpoint fpSpread.ocx with VB6 apps to duplicate my
Excel-based stuff. That control has no support for DV nor Group/Outline
(remember the TransactionLedger.xls I sent you some years back?) and so I had
to use VB to duplicate what Howard is trying to do here, but with dependent
dropdowns.

--
Garry

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

Code Count the items in a named range
 

Given that the dynamic range name def uses COUNTA() on colC instead of COUNT()
on colB, it deviates from the logic of the Contextures example.

Since this project was (obviously) derived from a copy of the Contextures
sample file, someone deliberately modified the name def[s] from the sample to
not use helper colB; -this defeats the intent use of the sample, making
ancilliary calcs more complicated than need be.

I recommend reverting the name defs back to the original Contextures example as
my linked file is done.
HTH

--
Garry

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