Populating an Array
Posted by Chris on September 20, 2000 3:35 PM
Within a function, I am trying to populate an array with "unique" worksheet data. The data are contained in one column but include duplicates that I am trying to eliminate. Any suggestions???
Posted by Chris on September 21, 2000 9:03 AM
Thank you for the reference. It looks like a great resource. However, I am trying to create an array within a function that I can utilize in other section of the function. Any suggestions?
Posted by Celia on September 21, 2000 4:38 PM
Chris
Try the following. It assumes that the selection is a single range in one column and the selection has already been sorted.
Sub UniqueItems()
Dim array1 As Variant
Dim array2
Dim J As Long, K As Long
array1 = Application.Transpose(Selection)
J = 0
For K = LBound(array1) To UBound(array1)
If J = 0 Then
J = 1
ReDim array2(1 To 1)
array2(K) = array1(K)
Else
If array1(K) <> array1(K - 1) Then
J = J + 1
ReDim Preserve array2(1 To J)
array2(J) = array1(K)
End If
End If
Next
End Sub
Celia
Posted by Chris on September 22, 2000 5:18 AM
Perfect!!! Thank you ever so much for your help. Additionally, as a novice programmer, one item that is uncertain to me is why you have re-dimension the lower and upper bounds for array2(i.e., ReDim array2(1 to j) vs just the upper bound. Any explanation would be greatly appreciated. Again, thank you for support.
Posted by Celia on September 22, 2000 7:04 AM
Chris
I always ReDim the lower and upper bounds as a matter of habit, but a ReDim of only the upper bound achieves the same thing (I think).
Celia
Posted by Celia on September 21, 0100 1:33 AM
Chris
Have a look at :-
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique
There is worksheet formula at this site for extracting unique entries. Perhaps you can use this to do what you want?
Celia
