For r = 9 To 11 temp = Split(Sheet1.Cells(r, 7), ",") If UBound(temp) > 0 Then For i = 0 To UBound(temp) For j = i + 1 To UBound(temp) If temp(i) = temp(j) Then temp(j) = "" Next Next temp = Replace(Join(temp, ","), ",,", ",") Sheet1.Cells(r, 7) = IIf(Right(temp, 1) = ",", Left(temp, Len(temp) - 1), temp)
End If Next
End Sub
这个用数组公式就能完成,为什么非要VBA啊
Sub Getdata() Dim rng As Range, Dic As Object Dim Arr, k% Set Dic = CreateObject("Scripting.Dictionary") For Each rng In Range("G9", [G65536].End(3)) Arr = Split(rng, ",") For k = 0 To UBound(Arr) Dic(Arr(k)) = "" Next rng(1, 2) = Join(Dic.keys, ","): Dic.RemoveAll Next End Sub
'vba
Sub a()
Dim temp
For r = 9 To 11
temp = Split(Sheet1.Cells(r, 7), ",")
If UBound(temp) > 0 Then
For i = 0 To UBound(temp)
For j = i + 1 To UBound(temp)
If temp(i) = temp(j) Then temp(j) = ""
Next
Next
temp = Replace(Join(temp, ","), ",,", ",")
Sheet1.Cells(r, 7) = IIf(Right(temp, 1) = ",", Left(temp, Len(temp) - 1), temp)
End If
Next
End Sub
Sub Getdata()
Dim rng As Range, Dic As Object
Dim Arr, k% Set Dic = CreateObject("Scripting.Dictionary")
For Each rng In Range("G9", [G65536].End(3))
Arr = Split(rng, ",")
For k = 0 To UBound(Arr)
Dic(Arr(k)) = ""
Next
rng(1, 2) = Join(Dic.keys, ","): Dic.RemoveAll
Next
End Sub
Dic(Arr(k)) = ""
第一次看到这样用的, Ding ...........
MS OFFICE(ACCESS\EXCE\WORD等应用技术探讨与交流!技术群,请阅群论坛中的《踢人规则》