adding two arrays and getting unique from them

[schandran]

I have just shown a sample code

asr is my main program. The unique item function uniqueitems returns wrong
no. of unique items. Can anybody correct it. array1 and array2 are got from column 35 and 36 in a sheet

Sub asr()
array1 = farfrran(Range(Cells(1, 35), Cells(6, 35)))
array2 = farfrran(Range(Cells(1, 36), Cells(6, 36)))
array3 = appendarray(array1, array2)
'For i = 1 To UBound(array3)
'MsgBox array3(i)
'Next i
array4 = uniqueitems(array3)
For i = 1 To 1
MsgBox array4
Next i
End Sub

Function appendarray(array1 As Variant, array2 As Variant) As Variant
Dim array3() As Variant
x = UBound(array1, 1) + UBound(array2, 1)
u = UBound(array1, 1)
ReDim array3(1 To x) As Variant
For i = 1 To u
array3(i) = array1(i, 1)
Next i
For i = u + 1 To x
j = i - u
array3(i) = array2(j, 1)
Next i
appendarray = array3
End Function

Function uniqueitems(array1 As Variant) As Variant
Dim unique As Variant
Dim Numunique As Long
Dim found As Boolean
Numunique = 0
x = UBound(array1)
For i = 1 To x
found = False
For j = 1 To Numunique
If array1(i) = unique(j) Then
found = True
GoTo 10
End If
Next j
10
If found = False Then
Numunique = Numunique + 1
ReDim unique(1 To Numunique) As Variant
unique(Numunique) = array1(i)
End If
Next i
uniqueitems = Numunique
End Function

Function farfrran(range1 As Range) As Variant
farfrran = range1.Value
End Function

[Leith Ross]

Hello Schandran,

You can simplify your code by making use of the Collection object. The Collection object automatically checks for repeat values. This way you collect only unique values.

Example:

Public Function UniqueValues() As Variant

Dim Array1() As Variant
Dim colUnique As New Collection
Dim I As Integer
Dim Col As Integer
Dim Row As Integer

For Col = Columns("AI").Column To Columns("AJ").Column
For Row = 1 To 6
'Collection Raises an Error if Item is Duplicated
On Error Resume Next
colUnique.Add Cells(Row, Col).Value
Next Row
Next Col

'Load Array
ReDim Array1(colUnique.Count)
For I = 1 To colUnique.Count
Array1(I) = colUnique(I)
Next I

'Return the Array
UniqueValues = Array1()

End Function

If you have any problems or questions with the code, you can contact me via e-mail at [email protected].

Sincerely,
Leith Ross