Trying to list unique values in an arryay

[Guest]

Here is what I am trying to do. I have an array consisting of 6 columns and 40 rows. How can I look in that array and list the unique values of it. I don't want the count, but the unique values.

Thanks.
DRS

 

[AlfD]

Hi!

Easiest way is probably to put all the values into one column (o
another worksheet, for example) and use Data > Filter > Advance
Filter... to produce the unique values.

Al

 

[Dave Peterson]

One way is to use a Collection:

Option Explicit
Sub testme()

Dim uniqColl As New Collection
Dim myCell As Range
Dim iCtr As Long

With ActiveSheet
On Error Resume Next
For Each myCell In .Range("a1:f40")
If Trim(myCell.Value) <> "" Then
uniqColl.Add Item:=CStr(myCell.Value)
End If
Next myCell
On Error GoTo 0

For iCtr = 1 To uniqColl.Count
MsgBox uniqColl(iCtr)
'or whatever you want to do with it.
Next iCtr
End With
End Sub

 

[pikapika13]

I agree w/ Alf...but here's a cruder possibility: If you're trying t
find unique values for that entire array (vs. columns only) you ca
also try entering this formula in Cell G1:
=IF(COUNTIF($A$1:$F$40,A1)=1,A1,"")
Auto copy that over 6 columns and across and 40 down

 

[Alan Beban]

Here is what I am trying to do. I have an array consisting of 6 columns and 40 rows. How can I look in that array and list the unique values of it. I don't want the count, but the unique values.

Thanks.
DRS

I take it by "array" you mean a range of values. If the functions in
the freely downloadable file at http://home.pacbell.net/beban are
available to your workbook, the following, arrray entered into a column
of cells sufficently large to accommodate the unique values, will list them:

=ArrayUniques(A1:F40, True)

For a listing in a single row:

=ArrayUniques(A1:F40)

Alan Beban