|
发表于 2017-4-26 16:33
|
显示全部楼层
本楼为最佳答案
- Sub 合并()
- arr = Sheets(1).[a1].CurrentRegion
- brr = Sheets(2).[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) & "," & i
- Next
- For i = 2 To UBound(brr)
- d1(brr(i, 1)) = d1(brr(i, 1)) & "," & i
- Next
- ReDim crr(1 To UBound(arr) + UBound(brr), 1 To 5)
- [a2:e65536].Clear
- For Each x In d.keys
- xrr = Split(d(x), ","): n1 = UBound(xrr)
- yrr = Split(d1(x), ","): n2 = UBound(yrr)
- nmax = Application.Max(n1, n2)
- Cells(n + 2, 1).Resize(nmax).Merge
- Cells(n + 2, 2).Resize(nmax).Merge
- For k = 1 To nmax
- n = n + 1
- If k <= n2 Then
- k2 = yrr(k)
- crr(n, 1) = brr(k2, 1)
- crr(n, 2) = brr(k2, 2)
- crr(n, 3) = brr(k2, 3)
- End If
- If k <= n1 Then
- k1 = xrr(k)
- crr(n, 4) = arr(k1, 2)
- crr(n, 5) = arr(k1, 3)
- End If
- Next
- Next
- [a2].Resize(n, 5) = crr
- [a2].Resize(n, 5).Borders.LineStyle = 1
- End Sub
复制代码 |
评分
-
查看全部评分
|