VBAでワークシートの行間に、新たに行を挿入する方法を解説します。IF文と組み合わせれば、対象レコードをグループごとに集計する小計行を作る時にも使えるスキルです。
データを確認する時に、レコードとレコードの間に1行づつ挿入すると、データを目で追いやすくなりますよね。
では早速ですが、以下のコードを真似て書いてみましょう。
それでは、さっそく実行してみましょう。下のようになりましたでしょうか。
では、つづきましてコードのお話しになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Option Explicit '1行おきに空白行を挿入する Sub Sample024() Dim lngERow As Long '最終レコード行番号 Dim r As Long '行挿入用 '最終レコードの行番号を取得 lngERow = Range("A" & Rows.Count).End(xlUp).Row '最終レコード行番号から3行目のレコードまで繰り返し処理 For r = lngERow To 3 Step -1 'r行目全体に対し行挿入 Cells(r, 1).EntireRow.Insert Next End Sub |
9行目:lngERow = Range("A" & Rows.Count).End(xlUp).Row
本サイトではもうおなじみのレコード最終行番号を取得するVBAコードです。
12~17行目:For r = lngERow To 3 Step -1 ~ Next
ここは、繰り返し構文のFor ~ Nextの処理範囲になります。ただ、これまでの使い方と少し違います。今回は最終レコード行番号の「lngERow」から繰り返し処理が始まり、3行目のレコードで終わります。
「lngERow」は、サンプルシートの最終レコードの行番号です。サンプルシートを確認すると「16」ですよね。よって、今回の繰り返しは、r = 16で始まって、r = 3で終わるという、変数rの変化が1づつ減っていく使い方になります。
そして、最後の「Step -1」というのが、先に述べた「変数rの変化が1づつ減っていく」を実現させているVBAコードになります。この「Step」の応用例としては、VBAコードで1行置きに着色する記事の中で「Step 2」として使用しています。
このように「Step」を使うことにより、変数rの変化を、「1づつ増やす」以外にも指定することができます。For~Next文は「Stepが省略されている場合は1づつ増える」と理解してもいいですね。
15行目: Cells(r, 1).EntireRow.Insert
ここは、For ~ Next文の中で繰り返し実行されるVBAコードになります。ここで初登場の「.EntireRow.Insert」がありますね。
まず「.EntireRow」は、「.」の前にあるRangeオブジェクトの(今回の例ではCells(r, 1))プロパティ(属性)で、「行全体」を表しています。分かりやすく図でお話しすると以下のようなイメージになります。
まず太枠のセルは、それぞれRangeオブジェクトとして、Range("D1"), Range("C4"), Range("A7")と表せます。そして、それらRangeオブジェクトの行全体を表す「.EntireRow」、すなわちRange("D1").EntireRow(黄色セル=1行目のすべてのセル)、Range("C4").EntireRow(青色セル=4行目のすべてのセル),
Range("A7").EntireRow(緑色セル=7行目のすべてのセル)は、それぞれ図の色付きセル(1行分全部)のことを指しています。
そして、最後の「.Insert」ですが、英語「insert」の日本語の意味の通り「挿入」になります。つまり、「.Insert」の前の行全体に対して「挿入」を実行するということになります。
以上より、15行目のVBAコードをr=16 から 3まで実行すれば、冒頭の結果のように、1行置きに空白行が挿入された状態になります。これは、Excelシートの15行目から3行目まで、順番に14回行挿入を繰り返した結果と同じになります。
ところで、「なぜ今回はr=3からStep 2でやらなかったの?」という疑問が生じたかもしれませんね。確かにr=3からStep 2で、行挿入してやることも可能です。
ただその場合、途中に行挿入が何回か発生するため、はじめ16行目であった最終レコードの行番号が、17, 18, ・・・という感じでズレるように増えていきます。
この場合の対応方法としては、(あまりスマートではないですが、)For ~ Nextの実行回数を多めに、例えば50回くらいにした上で、事前に数えておいた挿入回数を満たしたらループを抜ける手もあります。
また動的に最終行を取得しながら、処理対象行が最終行になったらループを抜けるとか、他にもいろいろありますが、コードが今回のものよりも複雑になります。
同じ処理をするならシンプルなVBAコードがいいですよね。自分を含め、他人が見ても簡単に理解できるVBAコード。業務効率化のためにも、理解しやすいVBAコードを書きましょう。
まとめ
今回は、Excel表データに対し1行置きに空白行を挿入するVBAコードを紹介しました。VBAコードの内容としては、繰り返し構文の1つ、「For ~ Next」の「Step」のバリエーションについてもお話ししました。