|
发表于 2014-4-27 15:34
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr, ar, br, d, d2
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- For k = 1 To 13 Step 12
- arr = Sheet1.Cells(k, 1).Resize(5, 8)
- brr = Sheet1.Cells(k + 6, 1).Resize(5, 8)
- For i = 1 To UBound(arr)
- zf = Join(Application.Index(arr, i, 0), ",")
- d(zf) = d(zf) + 1
- d2(zf & "," & d(zf)) = i
- Next
- d.RemoveAll
- For i = 1 To UBound(brr)
- zf = Join(Application.Index(brr, i, 0), ",")
- d(zf) = d(zf) + 1
- If d2.exists(zf & "," & d(zf)) Then
- For j = 1 To UBound(brr, 2)
- brr(i, j) = ""
- arr(d2(zf & "," & d(zf)), j) = ""
- Next
- End If
- Next
- ReDim ar(1 To 5, 1 To 8)
- ReDim br(1 To 5, 1 To 8)
- s = 0
- For i1 = 1 To UBound(arr)
- zf = Join(Application.Index(arr, i1, 0), ",")
- If zf <> String(7, ",") Then
- s = s + 1
- For j1 = 1 To UBound(arr, 2)
- ar(s, j1) = arr(i1, j1)
- Next
- End If
- Next
- s2 = 0
- For i2 = 1 To UBound(brr)
- zf = Join(Application.Index(brr, i2, 0), ",")
- If zf <> String(7, ",") Then
- s2 = s2 + 1
- For j2 = 1 To UBound(brr, 2)
- br(s2, j2) = brr(i2, j2)
- Next
- End If
- Next
- Sheet2.Cells(k, 1).Resize(5, 8) = ar
- Sheet2.Cells(k + 6, 1).Resize(5, 8) = br
- d.RemoveAll
- d2.RemoveAll
- Erase ar: Erase br
- Next
- End Sub
复制代码 |
|