|
发表于 2012-11-25 19:56
|
显示全部楼层
本楼为最佳答案
- Sub 去AB重复1()
- Dim arr
- On Error Resume Next
- Dim dicA As Object, dicB As Object
- Set dicA = CreateObject("scripting.dictionary")
- Set dicB = CreateObject("scripting.dictionary")
-
- Dim arrA, arrB
- Dim rowA&, rowB&, a
- rowA = Range("a" & Rows.Count).End(xlUp).Row
- rowB = Range("b" & Rows.Count).End(xlUp).Row
- arrA = Range("a1:a" & rowA)
- arrB = Range("b1:b" & rowB)
-
- For Each a In arrA
- dicA(a) = a
- Next
-
- For Each a In arrB
- dicB(a) = a
- Next
-
- If dicA.Count < dicB.Count Then
- For Each a In dicA.keys
- If dicB.exists(a) Then
- dicB.Remove (a)
- dicA.Remove (a)
- End If
- Next
- Else
- For Each a In dicB.keys
- If dicA.exists(a) Then
- dicB.Remove (a)
- dicA.Remove (a)
- End If
- Next
- End If
-
- Range("a:b").Clear
- Range("a1").Resize(dicA.Count) = WorksheetFunction.Transpose(dicA.keys)
- Range("b1").Resize(dicB.Count) = WorksheetFunction.Transpose(dicB.keys)
- End Sub
- Sub 去AB重复2()
- Dim arr, a, i&
- On Error Resume Next
- Dim dicA As Object, dicB As Object
- Set dicA = CreateObject("scripting.dictionary")
- Set dicB = CreateObject("scripting.dictionary")
-
- arr = Range("a1").CurrentRegion
- For i = 1 To UBound(arr)
- dicA(arr(i, 1)) = ""
- dicB(arr(i, 2)) = ""
- Next
-
- If dicA.Count < dicB.Count Then
- For Each a In dicA.keys
- If dicB.exists(a) Then
- dicB.Remove (a)
- dicA.Remove (a)
- End If
- Next
- Else
- For Each a In dicB.keys
- If dicA.exists(a) Then
- dicB.Remove (a)
- dicA.Remove (a)
- End If
- Next
- End If
-
- Range("a:b").Clear
- Range("a1").Resize(dicA.Count) = WorksheetFunction.Transpose(dicA.keys)
- Range("b1").Resize(dicB.Count) = WorksheetFunction.Transpose(dicB.keys)
- End Sub
复制代码 |
|