Resize array based on number of 'used' elements

[John]

Hi there,

So here's my second question of the afternoon..................

I'm creating an array of unique words based on another list passed to the
procedure (see code below). This works ok but I'd like to resize the
resulting array (vUniqueList) to be the same number as the elements that are
not empty. So can anyone tell me if there's a way of doing this without
iterating through the array to test for empty elements?

Best regards

John



'--------------------------
'Sample passed as parameter
Dim sWordLists As String
sWordLists = "Carrot%Carrot%Rabbit"
'--------------------------

Dim itm As Variant
Dim vUniqueList As Variant

ReDim vUniqueList(UBound(Split(sWordLists, "%"))) As Variant

'Get unique list
For Each itm In Split(sWordLists, "%")
For i = 0 To UBound(vUniqueList)
If IsEmpty(vUniqueList(i)) = True Then
vUniqueList(i) = itm
Exit For
Else
If itm = vUniqueList(i) Then
Exit For
End If
End If
Next i
Next itm

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

'Sample passed as parameter
Dim sWordLists As String
Dim iCtr As Long
Dim wCtr As Long
Dim vUniqueList As Variant
Dim mySplit As Variant
Dim res As Variant

sWordLists = "Carrot%Carrot%Rabbit%3"
mySplit = Split(sWordLists, "%")

ReDim vUniqueList(LBound(mySplit) To UBound(mySplit))

wCtr = LBound(mySplit) - 1

For iCtr = LBound(mySplit) To UBound(mySplit)
res = Application.Match(mySplit(iCtr), vUniqueList, 0)
If IsError(res) Then
'not yet in vuniquelist
wCtr = wCtr + 1
vUniqueList(wCtr) = mySplit(iCtr)
End If
Next iCtr

ReDim Preserve vUniqueList(LBound(mySplit) To wCtr)

End Sub

You want another way?

John Walkenbach shows how to use a collection to get a unique list. He also
shows how that list can be sorted (you may want to do that):
http://j-walk.com/ss/excel/tips/tip47.htm

 

[John]

Thanks Dave. Makes perfect sense and the use of Match seems a tidier
method.

Thanks for your help.

Best regards

John