VBA to get List of Unique Items from column
 

As I have painfully learned, a 'Collection' may
prove your solution.

A Collection can only hold unique values. If you
attempt to add a non-unique value, then an error
will be thrown. As J. Walkenbach shows, this
error can be usefully employed.

See http://j-walk.com/ss/excel/tips/tip47.htm
and thanks to Tom Ogilvy.

Perhaps this may help a little. Perhaps not.

Regards.

"Tod" wrote in message
...
Hi,

I have a list of about 2000 records, but only about 12 are
unique. I want to use those unique values to set up a
loop. Currently I'm doing something like this:

i = 0
For Each Cell In ActiveSheet.Range("J2:J1725")
If i = 0 Then
ReDim Preserve ROArray(i)
ROArray(i) = Cell.Value
i = i + 1
Else
For j = 0 To UBound(ROArray)
If Cell.Value = ROArray(j) Then k = k + 1
Next j
If k = 0 Then
ReDim Preserve ROArray(i)
ROArray(i) = Cell.Value
i = i + 1
End If
k = 0
End If
Next Cell

This works fine. I just wonder if I'm missing something
much simpler.

tod


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003

VBA to get List of Unique Items from column
 

You haven't got that quite right. I think you mean a collection cannot
have items with duplicate keys.

"Stuart" wrote in message ...
As I have painfully learned, a 'Collection' may
prove your solution.

A Collection can only hold unique values. If you
attempt to add a non-unique value, then an error
will be thrown. As J. Walkenbach shows, this
error can be usefully employed.

See http://j-walk.com/ss/excel/tips/tip47.htm
and thanks to Tom Ogilvy.

Perhaps this may help a little. Perhaps not.

Regards.

"Tod" wrote in message
...
Hi,

I have a list of about 2000 records, but only about 12 are
unique. I want to use those unique values to set up a
loop. Currently I'm doing something like this:

i = 0
For Each Cell In ActiveSheet.Range("J2:J1725")
If i = 0 Then
ReDim Preserve ROArray(i)
ROArray(i) = Cell.Value
i = i + 1
Else
For j = 0 To UBound(ROArray)
If Cell.Value = ROArray(j) Then k = k + 1
Next j
If k = 0 Then
ReDim Preserve ROArray(i)
ROArray(i) = Cell.Value
i = i + 1
End If
k = 0
End If
Next Cell

This works fine. I just wonder if I'm missing something
much simpler.

tod


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003