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