|
求单列重复与不重复值
问题:
求高手写一段VBA代码:
1、根据A列的型号明细,在C列中求出对应不重复型号部分和
E列中求出对应重复型号部分。
2、如左边示例,明细A9出现2次,所以出现在重复型号中,其他型号未重复,则出现在C列中。
3、要求代码可以增加到65536行。
4、希望可以有较快的运算速度或者类似字典的方法。
请见附图和附件!谢谢!
- Sub 求单列重复与不重复值()
- Dim i&, r%, rr%, Arr, Arr1(), Arr2()
- Dim d, k, t
- Set d = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- Myr = [a65536].End(xlUp).Row
- Arr = Range("a1").CurrentRegion
- For i = 2 To UBound(Arr)
- d(Arr(i, 1)) = d(Arr(i, 1)) + 1
- Next
- k = d.keys
- t = d.items
- For i = 0 To UBound(k)
- If t(i) = 1 Then
- r = r + 1
- ReDim Preserve Arr1(1 To r)
- Arr1(r) = k(i)
- Else
- rr = rr + 1
- ReDim Preserve Arr2(1 To rr)
- Arr2(rr) = k(i)
- End If
- Next
- [c2:e100] = ""
- [c2].Resize(r, 1) = Application.Transpose(Arr1)
- [e2].Resize(rr, 1) = Application.Transpose(Arr2)
- Set d = Nothing
- End Sub
复制代码
|
|