今回はセルのコピーを高速で行う方法をご説明します。
コピーペーストって記述方法で処理が非常に遅くなる原因の一つです。
理由はVBAはセルへのインプットとアウトプットが非常に遅いため、コピーペーストも同様にインプットアウトプットを行っていることになりますので、例外ではありません。
そのため、コピーペーストが遅いなと思った方は一度コードを見直してみると良いかもしれません。
コピー貼り付けの基本的な方法は「セルのコピー、ペーストする方法」をご覧ください。
また、コピーペーストに限らず処理速度の高速化は「処理速度の高速化対策」をご覧ください。
1.通常のコピー貼り付け方法
まずは通常のコピー貼り付け方法で速度検証します。
次のようなA列に10000行にデータが入力されていて、B列に10000回ループでコピー貼り付けで転記します。
Sub Sumple1()
Dim i As Long
For i = 1 To 10000
Cells(i, 1).Copy
Cells(i, 2).PasteSpecial Paste:=xlPasteValues '値のみ貼り付け
Next i
End Sub
A列からB列へ10000個のデータをコピー貼り付けするのに「101.210秒」もかかりました。
「Application.ScreenUpdating = False」で描画を止めても「60.382815秒」でした。
2.高速でコピー貼り付けをする
高速方法その1 範囲を一括コピーして一括貼り付け
同じ処理を今度は高速で行いたいと思います。
Sub Sumple2()
Range(Cells(1, 1), Cells(10000, 1)).Copy
Cells(1, 2).PasteSpecial Paste:=xlPasteValues '値のみ貼り付け
End Sub
無駄なコードをすべて省いて、コピーするセルを範囲指定して一括で貼り付けました。
処理時間は「0.046875秒」です。
先ほどの101秒と比較すると2500倍も速くなりましたし、これだけ高速だとストレスもありません。
高速方法その2 一括コピーしてPasteを省略
貼り付けは「.PasteSpecial」自体を省略して次のように記述する事ができます。
Sub Sumple3()
Range(Cells(1, 1), Cells(10000, 1)).Copy Cells(1, 2)
End Sub
先ほどの「0.046875秒」がさらに短縮されて「0.03125秒」になりました。
さらにはコードも1行で済みますので、非常にシンプルかつ高速です。
高速方法その3 配列に入れて一括で転記
コピー範囲を配列に入れて、一括で貼り付けします。
Sub Sumple4()
Dim MyArray As Variant
MyArray = Range(Cells(1, 1), Cells(10000, 1))
Range(Cells(1, 2), Cells(10000, 2)) = MyArray
End Sub
さらに短縮されて「0.015625秒」でした。
高速方法その2の「0.03125秒」の約半分です。
3.まとめと速度比較結果
都度ループでコピー貼り付けをすると非常に遅くなります。
いかにセルへのインプットアウトプットが遅いかがわかります。
高速方法その1~3は誤差の範囲といえば誤差の範囲ですが、データ量や文字数が多くなれば、この誤差でも最終的な処理時間には大きな影響を与えます。
| 処理方法 | 処理速度 |
| 都度コピー貼り付け | 101.2秒 |
| 都度コピー貼り付け(描画停止) | 60.3秒 |
| 高速方法その1 範囲を一括コピーして一括貼り付け | 0.046秒 |
| 高速方法その2 一括コピーしてPasteを省略 | 0.031秒 |
| 高速方法その3 配列に入れて一括で転記 | 0.015秒 |
