|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 paciguard 于 2017-5-19 14:25 编辑
脑洞大开,不行硬拆分,实际测试1000000条数据,比循环节省近50秒:
Sub test()
Dim d As Variant, k As Variant, T As Variant
Dim r As Long
Dim t1
t1 = Timer
Set d = CreateObject("scripting.dictionary")
Columns(1).ClearContents
Columns(2).ClearContents
For r = 1 To 1000000
d(r) = r * 2
If (r Mod 30000) = 0 Then
If r = 30000 Then
k = Application.Transpose(d.keys())
T = Application.Transpose(d.items())
Range("A" & [a1000000].End(3).Row).Resize(UBound(k), 1) = k
Range("B" & [b1000000].End(3).Row).Resize(UBound(T), 1) = T
d.RemoveAll
Else
k = Application.Transpose(d.keys())
T = Application.Transpose(d.items())
Range("A" & [a1000000].End(3).Row + 1).Resize(UBound(k), 1) = k
Range("B" & [b1000000].End(3).Row + 1).Resize(UBound(T), 1) = T
d.RemoveAll
End If
ElseIf r = 1000000 Then
k = Application.Transpose(d.keys())
T = Application.Transpose(d.items())
Range("A" & [a1000000].End(3).Row + 1).Resize(UBound(k), 1) = k
Range("B" & [b1000000].End(3).Row + 1).Resize(UBound(T), 1) = T
End If
Next
MsgBox Timer - t1
End Sub
|
|