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

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

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

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

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

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

Ok, but that approach makes all entries in those dropdowns invalid because each
dropdown will auto-update to contain the new list. A user could change it and
thus mess up your control over the remaining list. Using a dialog (as a popup
window) as I described eliminates the ability to change the dropdowns after the
selection has been made. (just saying...)

--
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

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could change
it and thus mess up your control over the remaining list. Using a dialog (as
a popup window) as I described eliminates the ability to change the dropdowns
after the selection has been made. (just saying...)

...because the dialog puts the list item into the cell, ergo no dropdown!

--
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 9:51:51 PM UTC-8, GS wrote:
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

Ok, but that approach makes all entries in those dropdowns invalid because each
dropdown will auto-update to contain the new list. A user could change it and
thus mess up your control over the remaining list. Using a dialog (as a popup
window) as I described eliminates the ability to change the dropdowns after the
selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range Cable_1.

I will then write some alert code.

Howard

On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could change
it and thus mess up your control over the remaining list. Using a dialog
(as a popup window) as I described eliminates the ability to change the
dropdowns after the selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which is
exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range will
auto-update their lists AS THE RANGE IS EDITED! What's to stop a user from
changing a previous selection?

--
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 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could change
it and thus mess up your control over the remaining list. Using a dialog
(as a popup window) as I described eliminates the ability to change the
dropdowns after the selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which is
exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range will
auto-update their lists AS THE RANGE IS EDITED! What's to stop a user from
changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a previous entry, clearing all drop downs will reset the entire named range.

Howard

On Thursday, December 28, 2017 at 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could
change it and thus mess up your control over the remaining list. Using a
dialog (as a popup window) as I described eliminates the ability to
change the dropdowns after the selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which
is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range
will auto-update their lists AS THE RANGE IS EDITED! What's to stop a user
from changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a
previous entry, clearing all drop downs will reset the entire named range.

Howard

Clearing the dropdowns only removes the cell contents, not the range they ref.
How will a changed dropdown reset the range?

--
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 10:48:52 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could
change it and thus mess up your control over the remaining list. Using a
dialog (as a popup window) as I described eliminates the ability to
change the dropdowns after the selection has been made. (just saying...)

--
Garry


Yes, each new drop down added will have the updated named range list which
is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range
will auto-update their lists AS THE RANGE IS EDITED! What's to stop a user
from changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a
previous entry, clearing all drop downs will reset the entire named range.

Howard

Clearing the dropdowns only removes the cell contents, not the range they ref.
How will a changed dropdown reset the range?

--
Garry


Formulas on the other sheet. All the items are restored to the named range column. It is a fairly common "...one selection only from a drop down list" usage.

Howard

Formulas on the other sheet. All the items are restored to the named range
column. It is a fairly common "...one selection only from a drop down list"
usage.

Are you refering to a "Hide Used Items in a Dropdown List" implementation such
as exampled on the Contextures website?

--
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 Friday, December 29, 2017 at 1:59:03 AM UTC-8, GS wrote:
Formulas on the other sheet. All the items are restored to the named range
column. It is a fairly common "...one selection only from a drop down list"
usage.

Are you refering to a "Hide Used Items in a Dropdown List" implementation such
as exampled on the Contextures website?

--
Garry


I don't know if that is true or not.

I have no intentions to change the method of the drop downs, my post here was to find a way to count the number of items in a named range.

I am unable to make the solutions offered work for me, but I will revisit them.

Thanks for looking in.

Howard

On Friday, December 29, 2017 at 1:59:03 AM UTC-8, GS wrote:
Formulas on the other sheet. All the items are restored to the named range
column. It is a fairly common "...one selection only from a drop down
list" usage.

Are you refering to a "Hide Used Items in a Dropdown List" implementation
such as exampled on the Contextures website?

--
Garry


I don't know if that is true or not.

I have no intentions to change the method of the drop downs, my post here was
to find a way to count the number of items in a named range.

I should have recognized the formula as I looked at using that some years ago.
Unfortunately, the examples didn't work in my VB6 spreadsheet control (no
support for DV) so I had to revert to using code to duplicate it.

I am unable to make the solutions offered work for me, but I will revisit
them.

Perhaps...

=COUNTA(Cable_1)

Thanks for looking in.

Howard

--
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 Howard,

Am Fri, 29 Dec 2017 05:23:03 -0800 (PST) schrieb L. Howard:

I am unable to make the solutions offered work for me, but I will revisit them.

why don't the suggestions for the named range work?
Can you offer the workbook?

To count the items try:
=COUNTA(Cable_1)-COUNTBLANK(Cable_1)


Regards
Claus B.
--
Windows10
Office 2016

On Friday, December 29, 2017 at 6:07:31 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 05:23:03 -0800 (PST) schrieb L. Howard:

I am unable to make the solutions offered work for me, but I will revisit them.

why don't the suggestions for the named range work?
Can you offer the workbook?

To count the items try:
=COUNTA(Cable_1)-COUNTBLANK(Cable_1)


Regards
Claus B.
-- Hi Claus,

Yes, that does work, I don't think you included that formula in your suggestions
but it works for me on the worksheet.

Would I use Worksheetfunction.COUNTA(Cable_1)-COUNTBLANK(Cable_1) in the code?

Howard

Hi Howard,

Am Fri, 29 Dec 2017 06:18:15 -0800 (PST) schrieb L. Howard:

Would I use Worksheetfunction.COUNTA(Cable_1)-COUNTBLANK(Cable_1) in the code?

try:
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

But I would prefer to modify the range name to the correct range.
Do you have blank cells between the values or are the blank cells only
at the end of the range?

Regards
Claus B.
--
Windows10
Office 2016

On Friday, December 29, 2017 at 6:27:14 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 06:18:15 -0800 (PST) schrieb L. Howard:

Would I use Worksheetfunction.COUNTA(Cable_1)-COUNTBLANK(Cable_1) in the code?

try:
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

But I would prefer to modify the range name to the correct range.
Do you have blank cells between the values or are the blank cells only
at the end of the range?


I tried it like this and the "myCnt =" line errors yellow.

Dim myCnt As Long
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

The blank cells are at the end. The end cell could vary but still all blanks will be at the end.

Howard

Hi Howard,

Am Fri, 29 Dec 2017 06:37:22 -0800 (PST) schrieb L. Howard:

I tried it like this and the "myCnt =" line errors yellow.

Dim myCnt As Long
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

if Cable_1 is a worksheet scope name you must insert the sheet name in
front of the range.

The blank cells are at the end. The end cell could vary but still all blanks will be at the end.

Then try for Cable_1 range name:
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C,0)-2)


Regards
Claus B.
--
Windows10
Office 2016

Hi Howard,

Am Fri, 29 Dec 2017 06:18:15 -0800 (PST) schrieb L. Howard:

Would I use Worksheetfunction.COUNTA(Cable_1)-COUNTBLANK(Cable_1) in the
code?

try:
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

But I would prefer to modify the range name to the correct range.
Do you have blank cells between the values or are the blank cells only
at the end of the range?

Regards
Claus B.

Since Cable_1 is a dynamic range, CountA will return its count; CountBlank will
always return zero!

--
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 Garry,

Am Fri, 29 Dec 2017 09:51:45 -0500 schrieb GS:

Since Cable_1 is a dynamic range, CountA will return its count; CountBlank will
always return zero!

Cable_1 is not really dynamic because Howard has formulas in the range
that write a nullstring. Cable_1 includes all cells with formulas. That
means it includes cells with nullstring.


Regards
Claus B.
--
Windows10
Office 2016

Hi Garry,

Am Fri, 29 Dec 2017 09:51:45 -0500 schrieb GS:

Since Cable_1 is a dynamic range, CountA will return its count; CountBlank
will always return zero!

Cable_1 is not really dynamic because Howard has formulas in the range
that write a nullstring. Cable_1 includes all cells with formulas. That
means it includes cells with nullstring.


Regards
Claus B.

Well, that's not the case when using CountA in the Contextures example file.
Yes, all the dynamic range cells have a formula, but if their return is null
then CountA doesn't count them. IOW, it only counts cells that have values
resulting from the formulas.

--
Garry

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

Have a look here at the bottom of the page for example files to download...

http://www.contextures.com/xlDataVal03.html

--
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 Friday, December 29, 2017 at 6:43:45 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 06:37:22 -0800 (PST) schrieb L. Howard:

I tried it like this and the "myCnt =" line errors yellow.

Dim myCnt As Long
With Application
myCnt = .CountA(Range("Cable_1")) - .CountBlank(Range("Cable_1"))
End With

if Cable_1 is a worksheet scope name you must insert the sheet name in
front of the range.

The blank cells are at the end. The end cell could vary but still all blanks will be at the end.

Then try for Cable_1 range name:
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C,0)-2)


Regards
Claus B.
--

Hi Claus.

The Cable_1 name is Workbook scope.

This is a worksheet formula, right? I forget what Talelle1 should be on my sheet.
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C,0 )-2)

Howard

Hi Howard,

Am Fri, 29 Dec 2017 07:07:17 -0800 (PST) schrieb L. Howard:

This is a worksheet formula, right? I forget what Talelle1 should be on my sheet.
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C, 0)-2)

change Tabelle1 to Sheet1


Regards
Claus B.
--
Windows10
Office 2016

Hi Garry,

Am Fri, 29 Dec 2017 10:04:40 -0500 schrieb GS:

Have a look here at the bottom of the page for example files to download...

http://www.contextures.com/xlDataVal03.html

I know, but Howard use CountA in column C with the formulas:
From his very first post:
=OFFSET('Cable Lists'!$C$2,0,0,COUNTA('Cable Lists'!$C:$C),1)

Therefore I offered
=OFFSET(Sheet1!$B$2,,1,COUNTA(Sheet1!$B:$B)-1)
for the named range. But he couldn't get it to work.
Now I try it with:
=OFFSET(Sheet1!$C$2,,,MATCH("",Sheet1!$C:$C,0)-2)



Regards
Claus B.
--
Windows10
Office 2016

On Friday, December 29, 2017 at 7:09:07 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 07:07:17 -0800 (PST) schrieb L. Howard:

This is a worksheet formula, right? I forget what Talelle1 should be on my sheet.
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C, 0)-2)

change Tabelle1 to Sheet1


Regards
Claus B.
--

Hi Claus,

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

These both work on the sheet.
=COUNTA(Cable_1)-COUNTBLANK(Cable_1)
=COUNTIF(C2:C100,"?*")

I could probably use the COUNTIF in the code for my Item Count.

Howard

Hi Garry,

Am Fri, 29 Dec 2017 10:04:40 -0500 schrieb GS:

Have a look here at the bottom of the page for example files to download...

http://www.contextures.com/xlDataVal03.html

I know, but Howard use CountA in column C with the formulas:
From his very first post:
=OFFSET('Cable Lists'!$C$2,0,0,COUNTA('Cable Lists'!$C:$C),1)

Therefore I offered
=OFFSET(Sheet1!$B$2,,1,COUNTA(Sheet1!$B:$B)-1)
for the named range. But he couldn't get it to work.
Now I try it with:
=OFFSET(Sheet1!$C$2,,,MATCH("",Sheet1!$C:$C,0)-2)



Regards
Claus B.

The sample file defines the dynamic range as...

=OFFSET(Employees!$C$1,0,0,COUNT(Employees!$B$1:$B $6),1)

...which is essentially the same as his is defined. I suspect his sheet has
headers since he starts at row2, which is why I rewrote it starting with row1
and subtracting 1 from the count. Symantics of personal preference, but I like
to ref the header as the base for offsetting but remove it from the count.
Ergo...

Name: Cable1_Hdr
RefersTo: =$C$1
ColumnLabel: Cable1

Name: Cable1
RefersTo: =OFFSET(Cable1_Hdr,1,0,COUNTA($C:$C)-1,1)

...and anywhere on the sheet...

=COUNTA(Cable1)

...which automatically updates as the list changes!

--
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 Friday, December 29, 2017 at 7:09:07 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 07:07:17 -0800 (PST) schrieb L. Howard:

This is a worksheet formula, right? I forget what Talelle1 should be on my
sheet.
=OFFSET(Tabelle1!$C$2,,,MATCH("",Tabelle1!$C:$C,0)-2)

change Tabelle1 to Sheet1


Regards
Claus B.
--

Hi Claus,

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

These both work on the sheet.
=COUNTA(Cable_1)-COUNTBLANK(Cable_1)
=COUNTIF(C2:C100,"?*")

I could probably use the COUNTIF in the code for my Item Count.

Howard

You have to wrap the sheet name in apostrophes becaue it contains a space.<g

--
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 Howard,

Am Fri, 29 Dec 2017 07:41:56 -0800 (PST) schrieb L. Howard:

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

you have spaces in the sheet name. So you must set the name in
apostrophs:
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

If in column B are only constants you could also try:
=OFFSET('Cable Lists'!$B$2,,1,COUNTA('Cable Lists'!$B:$B)-1)


Regards
Claus B.
--
Windows10
Office 2016

These both work on the sheet.
=COUNTA(Cable_1)-COUNTBLANK(Cable_1)
=COUNTIF(C2:C100,"?*")

I could probably use the COUNTIF in the code for my Item Count.

CountA returns the unused count; is this what you want?

--
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 Friday, December 29, 2017 at 7:48:50 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 07:41:56 -0800 (PST) schrieb L. Howard:

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

you have spaces in the sheet name. So you must set the name in
apostrophs:
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

If in column B are only constants you could also try:
=OFFSET('Cable Lists'!$B$2,,1,COUNTA('Cable Lists'!$B:$B)-1)


Regards
Claus B.
--

Hi Claus,

This returns one of the value in the column, about six rows down.
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

The value in B are the result of formulas, not constants.

Here is the workbook.
Working with Contractor A only at this point.

https://www.dropbox.com/s/jlhevtd91d...OX.xlsm ?dl=0

Howard

On Friday, December 29, 2017 at 7:48:50 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 07:41:56 -0800 (PST) schrieb L. Howard:

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

you have spaces in the sheet name. So you must set the name in
apostrophs:
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

If in column B are only constants you could also try:
=OFFSET('Cable Lists'!$B$2,,1,COUNTA('Cable Lists'!$B:$B)-1)


Regards
Claus B.
--

Hi Claus,

This returns one of the value in the column, about six rows down.
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

The value in B are the result of formulas, not constants.

Here is the workbook.
Working with Contractor A only at this point.

https://www.dropbox.com/s/jlhevtd91d...OX.xlsm ?dl=0

Howard

This project's lists are not setup the same way the Contexture example is.

--
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 Friday, December 29, 2017 at 7:48:50 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 29 Dec 2017 07:41:56 -0800 (PST) schrieb L. Howard:

On my sheet this returns #NAME?
=OFFSET(Cable Lists!$C$2,,,MATCH("",Cable Lists!$C:$C,0)-2)

you have spaces in the sheet name. So you must set the name in
apostrophs:
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

If in column B are only constants you could also try:
=OFFSET('Cable Lists'!$B$2,,1,COUNTA('Cable Lists'!$B:$B)-1)


Regards
Claus B.
--

Hi Claus,

This returns one of the value in the column, about six rows down.
=OFFSET('Cable Lists'!$C$2,,,MATCH("",'Cable Lists'!$C:$C,0)-2)

The value in B are the result of formulas, not constants.

Here is the workbook.
Working with Contractor A only at this point.

https://www.dropbox.com/s/jlhevtd91d...OX.xlsm ?dl=0

Howard

Rebuilding the defined names as per the Contextures examples makes it work as
their's does!

--
Garry

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

Have a look...

https://app.box.com/s/dvq9v2u1a27opwugq96sm5s4zsl6pm30

--
Garry

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