i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

Not to my knowledge. I have larger collections working, not sure what is or
controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

i went through a range of 20k+ records to add unique items to a collection
and it always stopped at 256. there are 322 in the list. i just used an
advanced filter for now, to copy the unique values and add them to an array.

so, it doesn't really matter, i was just curious and i thought i had used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

Here's a macro that has no problem adding 64K items to a collection although
it takes a minute or so to run. Perhaps your dataset had 256 unique values
and no more, so it was capping there. I commented out the key assignment in
the test macro, but it would work uncommented too since all the values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i went through a range of 20k+ records to add unique items to a collection
and it always stopped at 256. there are 322 in the list. i just used an
advanced filter for now, to copy the unique values and add them to an
array.

so, it doesn't really matter, i was just curious and i thought i had used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

This might be a little picky but you should shorten the range to the
used range in coulmn A rather than test every cell, also doing it like
below allows forward compatability


Code:
--------------------
Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub
--------------------


Tim Zych;393786 Wrote:
Here's a macro that has no problem adding 64K items to a collection
although
it takes a minute or so to run. Perhaps your dataset had 256 unique
values
and no more, so it was capping there. I commented out the key
assignment in
the test macro, but it would work uncommented too since all the values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
tool.' (http://www.higherdata.com)
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i went through a range of 20k+ records to add unique items to a
collection
and it always stopped at 256. there are 322 in the list. i just used
an
advanced filter for now, to copy the unique values and add them to an
array.

so, it doesn't really matter, i was just curious and i thought i had
used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure
what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003






--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109977

i see where my confusion set it, in the locals window all it displayed was
256 items, when in actuality, all 322 items are in the collection.

--

Gary Keramidas
Excel 2003


"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

It is not "production" code. It's a test macro to prove that the collection
loads 65536 items.

Of course it can be written more robustly. That's not the point.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Simon Lloyd" wrote in message
...

This might be a little picky but you should shorten the range to the
used range in coulmn A rather than test every cell, also doing it like
below allows forward compatability


Code:
--------------------
Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub
--------------------


Tim Zych;393786 Wrote:
Here's a macro that has no problem adding 64K items to a collection
although
it takes a minute or so to run. Perhaps your dataset had 256 unique
values
and no more, so it was capping there. I commented out the key
assignment in
the test macro, but it would work uncommented too since all the values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
tool.' (http://www.higherdata.com)
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i went through a range of 20k+ records to add unique items to a
collection
and it always stopped at 256. there are 322 in the list. i just used
an
advanced filter for now, to copy the unique values and add them to an
array.

so, it doesn't really matter, i was just curious and i thought i had
used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure
what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003






--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=109977

Simon:

I re-read my reply to you and it sounds kind of blunt. To clarify, I agree
with End(xlup) etc for when code should be migrated to production. Your
change creates a different operating result. Whereas my example populates
65536 rows, yours changes data in the userdrange. It's actually quite
different than my macro.

Regards,

Tim


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
It is not "production" code. It's a test macro to prove that the
collection loads 65536 items.

Of course it can be written more robustly. That's not the point.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Simon Lloyd" wrote in message
...

This might be a little picky but you should shorten the range to the
used range in coulmn A rather than test every cell, also doing it like
below allows forward compatability


Code:
--------------------
Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub
--------------------


Tim Zych;393786 Wrote:
Here's a macro that has no problem adding 64K items to a collection
although
it takes a minute or so to run. Perhaps your dataset had 256 unique
values
and no more, so it was capping there. I commented out the key
assignment in
the test macro, but it would work uncommented too since all the values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
tool.' (http://www.higherdata.com)
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i went through a range of 20k+ records to add unique items to a
collection
and it always stopped at 256. there are 322 in the list. i just used
an
advanced filter for now, to copy the unique values and add them to an
array.

so, it doesn't really matter, i was just curious and i thought i had
used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure
what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003






--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=109977

Tim, i appreciate you posting back, as it happens i just revisited then
thread and thought to myself "what a numpty!" (me i meant), changing
both ranges gave a collection of 1!, sorry about that but i was only
trying to highlight how to speed it up by only dealing with the used
range dynamically!

No apology or redress needed :)

Tim Zych;394412 Wrote:
Simon:

I re-read my reply to you and it sounds kind of blunt. To clarify, I
agree
with End(xlup) etc for when code should be migrated to production. Your
change creates a different operating result. Whereas my example
populates
65536 rows, yours changes data in the userdrange. It's actually quite
different than my macro.

Regards,

Tim


--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
tool.' (http://www.higherdata.com)
Workbook Compare - Excel data comparison utility

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
It is not "production" code. It's a test macro to prove that the
collection loads 65536 items.

Of course it can be written more robustly. That's not the point.

--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
tool.' (http://www.higherdata.com)
Workbook Compare - Excel data comparison utility

"Simon Lloyd" wrote in message
...

This might be a little picky but you should shorten the range to the
used range in coulmn A rather than test every cell, also doing it
like
below allows forward compatability


Code:
--------------------
Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub
--------------------


Tim Zych;393786 Wrote:
Here's a macro that has no problem adding 64K items to a collection
although
it takes a minute or so to run. Perhaps your dataset had 256 unique
values
and no more, so it was capping there. I commented out the key
assignment in
the test macro, but it would work uncommented too since all the
values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
'Compare Excel data with Workbook Compare Pro. Flexible Excel
compare
tool.' ('Compare Excel data with Workbook Compare Pro. Flexible
Excel compare tool.' (http://www.higherdata.com))
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i went through a range of 20k+ records to add unique items to a
collection
and it always stopped at 256. there are 322 in the list.i just
used
an
advanced filter for now, to copy the unique values and add them to
an
array.

so, it doesn't really matter, i was just curious and i thought i
had
used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" wrote in message
...
Not to my knowledge. I have larger collections working, not sure
what is
or controls the upper limit?

--

Regards,
Nigel




"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
i can't seem to remember, can a collection only hold 256
elements?

--

Gary Keramidas
Excel 2003






--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:
'collection question - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=109977)





--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109977