Remove Duplicate Array Items
 

Hi all,

I am trying to figure out how to remove duplicate values from an arra
in VBA. Can anyone point me in the right direction?

Thanks!
Eri

--
erikc
-----------------------------------------------------------------------
erikcw's Profile: http://www.excelforum.com/member.php...fo&userid=2879
View this thread: http://www.excelforum.com/showthread.php?threadid=48770

Remove Duplicate Array Items
 

One way:

Option Explicit
Sub testme()

Dim myArr As Variant
Dim myColl As Collection
Dim iCtr As Long
myArr = Array("a", "B", 99, "a", "A")

Set myColl = New Collection

On Error Resume Next
For iCtr = LBound(myArr) To UBound(myArr)
myColl.Add myArr(iCtr), CStr(myArr(iCtr))
Next iCtr
On Error Resume Next

ReDim myArr(LBound(myArr) To LBound(myArr) + myColl.Count - 1)

For iCtr = 1 To myColl.Count
myArr(LBound(myArr) + iCtr - 1) = myColl.Item(iCtr)
Next iCtr

'did it work
For iCtr = LBound(myArr) To UBound(myArr)
Debug.Print myArr(iCtr)
Next iCtr

End Sub

John Walkenbach shows a way to sort that collection at:
http://j-walk.com/ss/excel/tips/tip47.htm





erikcw wrote:

Hi all,

I am trying to figure out how to remove duplicate values from an array
in VBA. Can anyone point me in the right direction?

Thanks!
Erik

--
erikcw
------------------------------------------------------------------------
erikcw's Profile: http://www.excelforum.com/member.php...o&userid=28797
View this thread: http://www.excelforum.com/showthread...hreadid=487707

--

Dave Peterson