collection question

[Gary Keramidas]

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

 

[Nigel]

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

 

[Gary Keramidas]

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.

 

[Tim Zych]

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

 

[Simon Lloyd]

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

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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Gary Keramidas]

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.

 

[Tim Zych]

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.

 

[Tim Zych]

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

 

[Simon Lloyd]

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

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


tool.' (http://www.higherdata.com)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)