Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #110394
| From | Claus Busch <claus_busch@t-online.de> |
|---|---|
| Newsgroups | microsoft.public.excel.programming |
| Subject | Re: Code Count the items in a named range |
| Date | 2017-12-28 19:55 +0100 |
| Organization | A noiseless patient Spider |
| Message-ID | <p23en4$5v1$1@dont-email.me> (permalink) |
| References | <d956b0c7-cee7-472a-bae0-cd3aef93c1fb@googlegroups.com> |
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
your range name is not really dynamic. It includes all cells with the
formula and not only the cells with a value <> ""
Try for the name:
=OFFSET('Cable Lists'!$C$2,,,SUMPRODUCT(N(LEN('Cable Lists'!$C:$C)>1))-1)
Regards
Claus B.
--
Windows10
Office 2016
Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 10:24 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-28 13:48 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-28 13:58 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-28 14:44 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 18:12 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-28 21:47 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 20:04 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 00:51 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 00:54 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 22:13 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 01:18 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 22:44 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 01:48 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-28 23:10 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 04:58 -0500
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 05:23 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 08:34 -0500
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 15:07 +0100
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 06:18 -0800
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 15:27 +0100
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 06:37 -0800
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 15:43 +0100
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 07:07 -0800
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 16:09 +0100
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 07:41 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 10:45 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 10:49 -0500
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 16:48 +0100
Re: Code Count the items in a named range "L. Howard" <lhkittle@comcast.net> - 2017-12-29 07:59 -0800
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 11:39 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 13:45 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 14:18 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 09:51 -0500
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 15:55 +0100
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 10:00 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 10:04 -0500
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-29 16:16 +0100
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 10:44 -0500
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-31 18:26 -0500
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-28 19:55 +0100
Re: Code Count the items in a named range Claus Busch <claus_busch@t-online.de> - 2017-12-28 20:08 +0100
Re: Code Count the items in a named range "Peter T" <askformy@gmail.com> - 2017-12-29 20:20 +0000
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 15:34 -0500
Re: Code Count the items in a named range "Peter T" <askformy@gmail.com> - 2017-12-29 20:50 +0000
Re: Code Count the items in a named range GS <gs@v.invalid> - 2017-12-29 16:04 -0500
csiph-web