|
发表于 2016-2-17 09:23
|
显示全部楼层
本楼为最佳答案
野蛮女 发表于 2016-2-17 08:03
详见附件
谢谢
已调整,请测试- Sub 汇总()
- Dim arr, i&, d, d1, x, k&, imax&
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Sheets(2).[b2:k10000].Clear
- For k = 3 To Sheets.Count
- With Sheets(k)
- imax = .Cells(Rows.Count, 3).End(xlUp).Row
- .Range("a2:a" & imax) = .Name
- arr = .[a1].CurrentRegion
- For i = 2 To UBound(arr)
- x = arr(i, 2)
- If Len(x) Then
- If Not d.exists(x) Then
- Set d(x) = .Cells(i, 1).Resize(1, 9)
- d1(x) = ""
- Else
- Set d(x) = Union(d(x), .Cells(i, 1).Resize(1, 9))
- End If
- End If
- Next
- For Each x In d1.keys
- With Sheets(2)
- If d.exists(x) Then d(x).Copy .Cells(Rows.Count, 2).End(xlUp).Offset(1)
- End With
- Next
- d.RemoveAll
- d1.RemoveAll
- End With
- Next
- With Sheets(2)
- imax = .Cells(Rows.Count, 3).End(xlUp).Row
- With Sheets(2).Range("b1:k" & imax)
- .Sort key1:=[c1], order1:=xlAscending, Header:=xlYes
- End With
- End With
- End Sub
复制代码 |
|