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)+1COUNT(B$2:B$13),"",INDEX(A:A,SMALL(B$2: B$13,1+ROW(A2)-ROW(A$2))))

Thanks for taking a look.

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

The actual named range uses the formula like this, pulled down, to make a
continuous list.

=IF(ROW(A2)-ROW(A$2)+1COUNT(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

Code...

Method1:
Dim vList, lListCount&
vList = Range("Cable_1")
lListCount = UBound(vList)

Method2:
Dim lListCount&
lListCount = Worksheet.Function(Count(Range("Cable_1"))

Method3:
Dim lListCount&
lListCount = Range("Cable_1").Rows.Count

--
Garry

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

Correction:

=IFERROR(COUNT(Cable_1),0,COUNT(Cable_1))

S/B

=IF(ISERROR(COUNT(Cable_1)),0,COUNT(Cable_1))

--
Garry

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

Hi Claus and Garry,

I am not having any luck making any of the suggestions work for me.
I verified there is actually a named range Cable_1 and this formula is in the Refers to: box.
=OFFSET('Cable Lists'!$C$2,0,0,COUNTA('Cable Lists'!$C:$C),1)

On a sheet, I have a drop down in cell B2, (the starter).

I am using this change_event code to add a drop down on that sheet below the first and then the next if needed and the next and so forth. I want to limit the number of drop downs added to the number of items that are in the named range Cable_1.

The Cable_1 listed items are set up to allow a one time only selection of any item. The items in Cable_1 diminish by one each time a new drop down is added and a selection is made.

So, if Cable_1 has 12 items listed, there may be a need for only 4 drop downs. But I don't want to add more drop downs than the max number of item originally in Cable_1 should all the items be selected.

I will write some code in this change sub to alert when the last drop down is installed.


The code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Dim myCheck


With Target.Offset(1).Validation

myCheck = MsgBox("Add Drop Down?", vbQuestion + vbYesNo)
If myCheck = vbYes Then

.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Cable_1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

Target.Offset(1).Select

Else
MsgBox "No, exit"
Exit Sub
End If


End With
End Sub

Howard

Hi Howard,
So if I understand correctly, you want to have a fixed number of cells contain
unique entries in the Cable_1 list and remove those entries once assigned to a
cell until all entries in the list are used, thus destroying the dynamic range.

Using your _Change event should work fine for creating the dropdown in the cell
below Target. What you also need to do is delete the selected entry from the
Cable_1 dynamic range (Shift=xlUp) so the next dropdown doesn't include the
selected item.

When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Another way:
Have your _Change event open a dialog that lets the user make selections from a
combobox to populate the intended cell with no opportunity to change the
selection after the dropdown has been used to avoid messing up control over the
items remaining in the list.

--
Garry

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

On Thursday, December 28, 2017 at 6:48:08 PM UTC-8, GS wrote:
Hi Howard,
So if I understand correctly, you want to have a fixed number of cells contain
unique entries in the Cable_1 list and remove those entries once assigned to a
cell until all entries in the list are used, thus destroying the dynamic range.

Using your _Change event should work fine for creating the dropdown in the cell
below Target. What you also need to do is delete the selected entry from the
Cable_1 dynamic range (Shift=xlUp) so the next dropdown doesn't include the
selected item.

When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Another way:
Have your _Change event open a dialog that lets the user make selections from a
combobox to populate the intended cell with no opportunity to change the
selection after the dropdown has been used to avoid messing up control over the
items remaining in the list.

--
Garry


Hi Garry,

Yes to this question.
When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Ref: Removing items from Cable_1 as used.
On another sheet the named range is a product of this formula, pulled down. Where it provides a contiguous list of non selected items.

=IF(ROW(A2)-ROW(A$2)+1COUNT(B$2:B$13),"",INDEX(A:A,SMALL(B$2: B$13,1+ROW(A2)-ROW(A$2))))

Whenever an item is selected in a drop down, it is removed from Cable_1, (by the formula referencing other columns, A:A & B2:B13)

The formula makes a list from row 2 and on down for all remaining items yet to be selected.

Howard

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

Hi again,

Am Thu, 28 Dec 2017 19:55:32 +0100 schrieb Claus Busch:

=OFFSET('Cable Lists'!$C$2,,,SUMPRODUCT(N(LEN('Cable Lists'!$C:$C)1))-1)

or easier:
=OFFSET('Cable Lists'!$B$2,,1,COUNTA('Cable Lists'!$B:$B)-1)


Regards
Claus B.
--
Windows10
Office 2016

"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

"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

"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

"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