|
字典+动态数组- Sub 合并数据4()
- '字典+动态数组
- '使用动态数组
- Dim arr, arr2
- Dim arrRst '动态数组
- Dim i&, t!, k&, j&
- Dim wsh As Worksheet
- Dim dic As Object
- t = Timer
- Application.ScreenUpdating = False
- Set dic = CreateObject("scripting.dictionary")
- ReDim arrRst(1 To 3, 0)
- For Each wsh In Worksheets
- If wsh.Name Like "*[1-9]" Then
- With wsh
- If .[a1] = "" Then GoTo goout
- arr = .Range("a1").Resize(.[a1].End(xlDown).Row, 4)
- End With
- For i = 2 To UBound(arr)
- If Not dic.exists(arr(i, 1) & "|" & arr(i, 3)) Then
- k = k + 1
- dic.Add arr(i, 1) & "|" & arr(i, 3), k
- ReDim Preserve arrRst(1 To 3, k)
- arrRst(1, k) = arr(i, 1)
- arrRst(2, k) = arr(i, 3)
- arrRst(3, k) = arr(i, 4)
- Else
- j = dic(arr(i, 1) & "|" & arr(i, 3))
- arrRst(3, j) = arrRst(3, j) + arr(i, 4)
- End If
- Next
- End If
- goout: Next
- arr2 = Array("Elem", "DIP", "SF")
- For i = 0 To UBound(arr2)
- arrRst(i + 1, 0) = arr2(i)
- Next
- 'Debug.Print "字典+动态数组花费时间为: " & Timer - t & "秒"
- 't = Timer
- Worksheets("统计").Range("a1").Resize(UBound(arrRst), 3) = Application.Transpose(arrRst)
- Application.CutCopyMode = False
- Debug.Print "字典+动态数组写回单元格花费时间为: " & Timer - t & "秒"
- End Sub
复制代码 |
|