G
Gary Keramidas
i can't seem to remember, can a collection only hold 256 elements?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Nigel
Not to my knowledge. I have larger collections working, not sure what is or
controls the upper limit?
controls the upper limit?
G
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.
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.
T
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
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
G
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.
256 items, when in actuality, all 322 items are in the collection.
T
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.
loads 65536 items.
Of course it can be written more robustly. That's not the point.
T
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
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