|
发表于 2014-12-12 21:08
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr(1 To 60000, 1 To 200), d, i%, j&, k%, k2%
- Set d = CreateObject("scripting.dictionary")
- h = 1: l = 1
- For i = 1 To Sheets.Count
- If Sheets(i).Name <> "汇总" And Application.CountA(Sheets(i).UsedRange) > 1 Then
- arr = Sheets(i).Range("a1").CurrentRegion
- For k = 2 To UBound(arr, 2)
- If Not d.exists(arr(1, k)) Then
- l = l + 1
- d(arr(1, k)) = l
- brr(1, l) = arr(1, k)
- End If
- Next
- For j = 2 To UBound(arr)
- If Not d.exists(arr(j, 1)) Then
- h = h + 1
- d(arr(j, 1)) = h
- brr(h, 1) = arr(j, 1)
- For k2 = 2 To UBound(arr, 2)
- brr(h, d(arr(1, k2))) = arr(j, k2)
- Next
- Else
- h2 = d(arr(j, 1))
- For k3 = 2 To UBound(arr, 2)
- brr(h2, d(arr(1, k3))) = brr(h2, d(arr(1, k3))) + arr(j, k3)
- Next
- End If
- Next
- End If
- Next
- brr(1, 1) = "名称"
- Sheets("汇总").Activate
- Range("i1").Resize(h, l) = brr
- End Sub
复制代码 |
|