指定した数の行を一瞬で挿入するには?(Tips-6)
VBAの勉強を始めて、これまでに学んだことや、思いついたアレやコレをメモっておきます。
今回は、VBAで、ユーザーが指定した数の行を挿入する方法についてです。
まず、下のように、For ~Next を使って、指定した数の行を挿入させてみようと思います。
アクティブセルがある行の上方向に、行挿入する処理を n回 繰り返します。
Sub test1()
Dim n As Long
n = InputBox("挿入する行数を入力して下さい")
For i = 1 To n
ActiveCell.EntireRow.Insert xlShiftDown, xlFormatFromLeftOrAbove
Next i
End Sub
1~1,000行程度であれば、ほぼ一瞬で完了しますが、10,000行辺りから少し時間がかかるようになってきます。100,000行になると・・・・・・、
3分13秒
うーん。カップラーメンが出来てしまいますぅ(@_@;)
上記は、VBAの勉強を始めた初期の頃に作ったコードです。今見ると、腹パンしてやりたくなりますね。
前回も書きましたが、ループというのは掛け算であり、処理の内容によっては、とんでもなく時間がかかってしまうんです・・・・・・。
実際に100,000行も挿入するようなことはあまりありませんが、私はこのコードが気に入らず、コードを書いた当日はずっとモヤモヤした気分でした。
しかし、翌日ふと、次のような処理を思いつき、コードを修正しました。
Sub test2()
Dim n As Long
n = InputBox("挿入する行数を入力して下さい")
Rows(ActiveCell.Row & ":" & ActiveCell.Row + n - 1).Insert xlShiftDown, xlFormatFromLeftOrAbove
End Sub
アクティブセルがある行から下に n行 選択した状態で、行挿入を1回だけ行うのです。単純ですね。
エクセルの操作に例えるとこんな感じ、
2~11行目を(要するに2行目を始点にして10行分)選択した状態で「挿入」します。
すると、1行目と2行目の間に10行挿入されます。
上記のコードでやっているのは、これと同じようなことです。
このコードで、100,000行挿入した時の時間は・・・・・・、
0秒
なーんだ、ループを使わなくてもできる方法があるじゃないか。
test1,test2 について、処理にかかった時間をまとめると下表のようになりました。
挿入する行数 | test1 | test2 |
---|---|---|
10 | 0秒 | 0秒 |
100 | 0秒 | 0秒 |
1,000 | 1秒 | 0秒 |
10,000 | 9秒 | 0秒 |
100,000 | 3分13秒 | 0秒 |
1,000,000 | 計測不能 | 0秒 |
これで、test1のコードは実用に耐えないことが分かります。ある程度、VBAに慣れてくると、test1のようなコードは実行するまでもなく、時間がかかることが分かるようになってくるので、最初から別の方法でコーディングすることを考えるようになってきます。
初心者の私は、こんな感じで処理を効率化できたときの嬉しさが忘れられず、そして、これからも味わうために、プログラミングにハマっているのかもしれません。
今回のような例は、ExcelVBA ならではのものなのかもしれませんが(@_@;)
前回はワークシート関数でループ処理を代替し、今回はその他の方法でループ処理を代替してみました。次回は、ループをどうしても使わなければならない場合に、処理をできるだけ軽くする方法などを模索してみようと思います。