Code Count the items in a named range
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
|