try ... the alternative soluction:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
Dim rngU As Excel.Range
Static SalvaLoop As Boolean

Set rng = [a1:a20] '<< change with your range
If SalvaLoop Then Exit Sub

On Error Resume Next
Set rngU = Intersect(Target, rng)
On Error GoTo 0

SalvaLoop = True
If TypeName(rngU) = "Range" Then
For Each rng In rngU
rng.Value = _
Join( _
BoobleSort( _
Split( _
No_Duple_RE(CStr(rng.Value)), ";")), ";")
Next
End If
SalvaLoop = False
End Sub


Function No_Duple_RE(Testo As String) As String
Dim RE As Object
Dim M, s As String

Set RE = CreateObject("VBScript.RegExp")

RE.Global = True
RE.IgnoreCase = True
RE.Pattern = "[^;]+"
For Each M In RE.Execute(Testo)
RE.Pattern = "\b" & M & "\b"
If RE.Test(s) = False Then
s = s & ";" & M
End If
Next
RE.Pattern = "^;"
No_Duple_RE = RE.Replace(s, "")
End Function

Function BoobleSort(ByRef ArrB As Variant)
Dim i As Long, a As Long, v
Dim arrA
arrA = ArrB
For i = 0 To UBound(arrA) - 1
For a = i To UBound(arrA)
If arrA(i) arrA(a) Then
v = arrA(i)
arrA(i) = arrA(a)
arrA(a) = v
End If
Next
Next
BoobleSort = arrA
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Makelei" wrote:

Hi,
I am using 2003 and XP.

In active cell I need to do the following in Worksheet_Change(ByVal Target
As Range).

As person gives values it can be following:
"Value1; Value2; Value3; Value2; Value4; Value2; Value5"

I want to modify given value to format:
"Value1; Value2; Value3; Value4; Value5"

Values are delimited with ";" and amount varies. It means that all given
values can be listed ONLY once. I imagine that this would be best to do with
array. That is a part that I really should study more - any suggestion for
good source?

Thanks in advance
MakeLei