Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #110393
| From | GS <gs@v.invalid> |
|---|---|
| Newsgroups | microsoft.public.excel.programming |
| Subject | Re: Code Count the items in a named range |
| Date | 2017-12-28 13:48 -0500 |
| Organization | A noiseless patient Spider |
| Message-ID | <p23ea2$2or$1@dont-email.me> (permalink) |
| References | <d956b0c7-cee7-472a-bae0-cd3aef93c1fb@googlegroups.com> |
> 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
>
> The actual named range uses the formula like this, pulled down, to make a
> continuous list.
>
> =IF(ROW(A2)-ROW(A$2)+1>COUNT(B$2:B$13),"",INDEX(A:A,SMALL(B$2:B$13,1+ROW(A2)-ROW(A$2))))
>
> Thanks for taking a look.
>
> Howard
Couple of things about your dynamic range definition:
Dynamic ranges MUST be contiguous cells;
Your RefersTo formula includes row1 as part of the list;
I assume this is a header row and so...
=OFFSET($C$1,1,0,COUNTA($C:$C)-1,1)
..so it excludes the header in the range, but refs the header as the
offset origin.
As for the current number of rows in Cable_1...
=COUNT(Cable_1)
..will return the row count if any, an error if none because Cable_1 doesn't
exist until there's an item below its header. Otherwise...
=IFERROR(COUNT(Cable_1),0,COUNT(Cable_1))
..to return zero in case the list is empty.
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
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