【VBA】セル番号を使わず動的に複数行挿入する

概要

VBAで複数の行を挿入する方法を検索すると、次のような書き方が例として出てきます。

Rows("1:3").Insert
1～3行目に新しい行を3行挿入する。

Range("A1","B2").EntireRow.Insert
セルで指定するやり方。1～2行目に2行挿入する。
EntireRowなしの場合、A1～B2の2x2マスを挿入する。

実際にVBAを使うシーンでは動的にデータ件数分挿入するケースが多いため、これを次のように応用することができるでしょう。

Rows(startRow & ":" & endRow).Insert
startRow=1, endRow=3の場合、Rows("1:3").Insertと同じ処理。

あるいは、データ件数をカウントしてrowNumという変数に入れておいて、次のように書くかもしれません。

Rows(startRow & ":" & startRow + rowNum - 1 ).Insert

これでもシンプルに行挿入は実現できるのですが…

startRowこと開始セル番号、これを決め打ちしたくない！
入力欄が複数箇所存在するテンプレートの場合、データを挿入した行数分次の入力欄の開始行がズレてしまう！これをプログラム内で変数を使って管理していくのは大変すぎる！

…などとExcelテンプレートの形式によっては途方に暮れてしまうこともあるでしょう。（ありました）
ですが、なんとかソースコード内でセル番号を意識しない動的な行挿入を実現できました。

使用するのは、次の二つです。

• Excelの「名前の定義」機能
• VBAの「Offset」プロパティ

最終的に次のような書き方になります。

'rowNumはデータ行数
Range(Range("範囲1").Offset(1), Range("範囲1").Offset(rowNum - 1)).EntireRow.Insert

以下、手順や検証内容を詳しく説明していきます。

セル番号を排除するための2STEP

STEP1 「名前の定義」で基準となるセル範囲に名前を付ける

Excelの「名前の定義」を使うと、セル範囲に任意の名前をつけることができます。（セル単体も可能）
ここでつけた名前は、VBAのソースコード上で扱うことができます。また、一度名前を定義してしまえば、その後の処理によって最初の位置からずれてしまった場合でも、ずれ分を考慮して参照することができます。

手順

データを挿入する際に基準となる範囲を選択し、数式タブの「名前の定義」をクリックします。

セルに名前を付ける画面が出てくるので、"名前"に適当な名前を入力してOKします。
ここでつけた名前をソース上でも扱っていくことになります。（日本語でも大丈夫です）

名前を定義すると、範囲選択した際にExcel画面左上のプルダウンがセル番号ではなく定義名になります。

さてSTEP1、名前の定義はこれで完了です。

動作検証

名前定義した範囲は、Rangeの引数に渡すことで参照が可能です。
行が手前に挿入され本来の位置からずれた場合でも参照できているか確認してみます。

想定しているケースがわかりやすいよう、ヘッダと枠を付けました。
"テスト"と名前定義した範囲はA8～E8になっています。
青いヘッダのテーブルは前段の処理でデータ行数分下に拡張され、その分緑のヘッダのテーブルの開始行がずれるような状況を想定します。

次の関数をVBAで作成し実行します。

Sub hogehoge()
    'A4の行をコピー
    Range("A4").EntireRow.Copy
    '4～6行目に3行挿入
    Rows("4:6").Insert
    '"テスト"と定義した範囲に値を入れる
    Range("テスト").Value = Array(1, 2, 3, 4, 5)
End Sub

4～6行目に3行分挿入するので、文字は11行目に入力されるはずです。

結果

行番号がずれてもちゃんと正しい参照先に値がセットされていることがわかります。

STEP2 Offsetを使ってセルを相対的に指定する

さて、STEP1でセル番号から解放され、開始位置がずれることへの懸念はなくなりました。
次は名前定義した範囲から複数行を指定して挿入しなければなりません。
ここで、Range.Offsetプロパティを使用します。
Offsetを使うと、引数で指定した数だけ移動した位置を表現することができます。

.Offset (RowOffset, ColumnOffset)

例えば、Range("A1").Offset(1)はA1から1行下、A2セルを表します。
Range("A1").Offset(,1)はA1から1列右、B1セルを表します。

さて。もうお分かりかと思いますが、
Range("テスト").Offset(1)という記述で"テスト"と名付けた範囲から1行下のセル範囲を表せます。

少し冗長ですが、"テスト"と名付けた範囲の1行下から5行分挿入したい場合は次のような記述になります。
Range(Range("テスト").Offset(1), Range("テスト").Offset(5)).EntireRow.Insert

動作検証

STEP1の動作確認で行った処理を拡張して、
今度は値を入れるだけではなく行を2行挿入した上で値を入れてみます。

この処理でのデータ挿入により影響が出るさらに下のテーブルも想定して追加しました。

次の関数をVBAで作成し実行します。
少し処理が多いので、注目したい動的な行挿入の部分に★を付けています。
名前定義を使っている他には、明示的なセル番号の指定がないことがわかります。

Sub hogehoge()
    
    ' === 「別のテーブル」にデータが挿入された想定の処理 ===
    'A4の行をコピー
    Range("A4").EntireRow.Copy
    '4～6行目に3行挿入
    Rows("4:6").Insert

    ' === 「この動作確認で使うテーブル」へ値を挿入する処理 ===
    '挿入用データ準備
    Dim InputArray(1 To 3, 1 To 5) As Variant    
    'データの件数は3件
    Dim rowNum As Integer: rowNum = UBound(InputArray, 1)
    'データの列数は5件
    Dim colNum As Integer: colNum = UBound(InputArray, 2)
    ' 1, 1, 1, 1, 1
    ' 2, 2, 2, 2, 2
    ' 3, 3, 3, 3, 3
    Dim i, j As Integer
    For i = 1 To rowNum
        For j = 1 To colNum
        InputArray(i, j) = i
        Next
    Next    
    '★"テスト"と名前定義したセル範囲の行をコピー
    Range("テスト").EntireRow.Copy
    '★"テスト"行の下に複数行挿入
    Range(Range("テスト").Offset(1), Range("テスト").Offset(rowNum - 1)).EntireRow.Insert
    '★データをセット
    Range(Range("テスト"), Range("テスト").Offset(rowNum - 1)) = InputArray
End Sub

STEP1と同様に、11行目から値が入力されるはずです。
2行挿入し、3行分のデータを入力しています。

結果

この通り、動的に複数行データ列を挿入することができました！

データ入力欄が3つ、4つ…と増えても名前の定義さえあれば同様に対応可能です。
また、データ入力欄が上下だけでなく左右にも分散されているような場合は、
EntireRowを除いて必要な行列だけを挿入すれば大丈夫です。

おわりに

令和にVBA…！？となる方もいらっしゃるかもしれません。ですが、環境構築不要でホイッと渡せるExcelは優秀ですし、今後も経理総務の事務作業やちょっとしたデータの管理等々…様々なシーンで使われていくのではないかと思います。
他の言語と違ってクセ強めですが、ネットやMSのドキュメントを漁ってなんとか効率よく、いい感じに書けないかどうか探求していくのも意外と楽しいです。上手く使っていきたいものです。