1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| Sub MonString_A_Doublons()
Dim col, d$, dd$, S$, TabStrg$
col = Array(1, 3, 5)
ReDim Tabcol$(UBound(col))
For n& = 0 To UBound(col)
Tabcol(n) = Join(Application.Transpose(Range(Cells(1, col(n)), Cells(Rows.Count, col(n)).End(xlUp)).Value), ","): Tabcol(n) = Replace(Tabcol(n), ",,", ",") & ","
S = Tabcol(n)
For Each v In Split(Tabcol(n), ",")
d = InStr(S, v & ",")
If dd Like "*" & d & "," & "*" Then
Tabcol(n) = Left(Tabcol(n), InStr(1 + InStr(Tabcol(n), v & ","), Tabcol(n), v & ",") - 2) & Right(Tabcol(n), Len(Tabcol(n)) - InStr(1 + InStr(Tabcol(n), v & ","), Tabcol(n), v & ",") + 2 - Len(v) - 1)
Else
dd = dd & "," & InStr(S, v)
End If
Next
dd = "": TabStrg = Replace(TabStrg, ",,", ",") & "," & Tabcol(n)
Next
TabStrg = Replace(TabStrg, ",,", ","): S = TabStrg
For Each v In Split(TabStrg, ",")
If InStr(S, v & ",") > 0 And v > "" Then
d = InStr(TabStrg, v & ",")
If dd Like "*" & d & "," & "*" And InStr(S, v & ",") > 0 Then
Do
If InStr(S, v & ",") < InStr(1 + InStr(S, v & ","), S, v & ",") Then
S = Left(S, InStr(1 + InStr(S, v & ","), S, v & ",") - 2) & Right(S, Len(S) - InStr(1 + InStr(S, v & ","), S, v & ",") + 2 - Len(v) - 1)
Else
S = Left(S, InStr(S, v & ",") - 2) & Right(S, Len(S) - InStr(S, v & ",") + 2 - Len(v) - 1)
End If
Loop Until InStr(S, v & ",") = 0
Else
dd = dd & "," & InStr(TabStrg, v & ",")
End If
End If
Next
tabgen = Split(Mid(S, 2, Len(S) - 1), ",")
Cells(1, 8).Resize(UBound(tabgen), 1) = Application.Transpose(tabgen)
End Sub |
Partager