Prev: Handling different Error 1004 errors
Next: form combobox auto expand
From: Ryan H on 26 Jan 2010 08:54
How can I build an array with non repeating values from a column? For
example, in Col. A I have this:

Col. A
1
2
3
3
3
4
4

I want MyArray = Array(1,2,3,4). No duplications. Can I use the Split
Function?

MyArray = Split(MyRange, "", 1, ) ' this doesn't work, Err: Type Mismatch

Thanks in Advance!
--
Cheers,
Ryan
From: Bernd P on 26 Jan 2010 10:02
Hello Ryan,

Split splits a string, not a range.

I suggest to use Lfreq or another of my UDF's I provide:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd
From: Sam Wilson on 26 Jan 2010 10:13
Something like this would work. It's not very pretty:

Sub arraymaker()

Dim rng As Range
Set rng = Range("A1:A13")

Dim aResult() As String
Dim i As Integer

Dim c As Range
For Each c In rng
If WorksheetFunction.CountIf(Range("a1:" & c.Address), c) = 1 Then
ReDim Preserve aResult(i)
aResult(i) = c.Value
i = i + 1
End If
Next c

End Sub

"Ryan H" wrote:

> How can I build an array with non repeating values from a column? For
> example, in Col. A I have this:
>
> Col. A
> 1
> 2
> 3
> 3
> 3
> 4
> 4
>
> I want MyArray = Array(1,2,3,4). No duplications. Can I use the Split
> Function?
>
> MyArray = Split(MyRange, "", 1, ) ' this doesn't work, Err: Type Mismatch
>
> Thanks in Advance!
> --
> Cheers,
> Ryan
 | 
Pages: 1
Prev: Handling different Error 1004 errors
Next: form combobox auto expand