Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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



  #42   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
  #43   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #44   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
  #45   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation; add items to named range Ixtreme Excel Discussion (Misc queries) 5 July 17th 09 11:01 PM
Count items in range Steven Excel Worksheet Functions 16 July 23rd 08 02:57 PM
Count unique items in range Thom Excel Worksheet Functions 4 October 12th 06 01:13 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Selecting Filtered Items from Named range Soniya Excel Programming 2 August 20th 03 10:59 AM


All times are GMT +1. The time now is 03:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"