Excelの表へ行をシステマチックに挿入する

Excelシートに作成した表にあとから、例えば1行おきに行を挿入するというシチュエーションありませんか？えっ？ないですか？私はありました。

意外に融通が利かないExcelの行挿入

実際にやってみてください。1行おきの行挿入。

10行やそこらの表ならたいした手間ではないですが、これが100行ともなるとかなりハードです。どこかの行間に100行を一気に挿入というのであれば100行分を空いているスペースからコピーしてきて挿入という手段が使えますが、1行おきとなるとそうはいきません。99回の行の挿入操作をすることになります。

Excelの編集機能では1行おきに行挿入はできないはずです。できないですよね？できてしまうとこのネタが終わってしまうので、これ以上深入りしないことにして、このような単純作業の繰り返しはVBAのお家芸です。

次のような表があります。

内容はさいたま市のWEBサイトで公開されているExcelデータで、見やすく整形しています。

通常の手順で行挿入を1行おきにやるとなると、Ctrlキーを押しながら2～10行目を1行ずつ選択して、最後に行の挿入を実行することになります。

行が増えれば増えるほど、手数と時間をかけなければなりません。これをVBAでやってしまえば何行だろうと瞬殺です。

ということで、やってみました。

解説

行の挿入はRangeオブジェクトのInsertメソッドにより実現できます。Insertメソッドの公式リファレンスの説明（日本語訳）はこうです。

ワークシートまたはマクロシートの指定された範囲に、空白のセルまたはセル範囲を挿入します。指定された範囲にあったセルはシフトされます。

ということで、行を挿入するというよりは、1行分のセル範囲を突っ込んでいるということですね。

行に相当するRangeオブジェクトはWorksheetオブジェクトからRowsというプロパティで取得できます。Rowsプロパティはシートのすべての行を保持しているので、特定の行だけを取得するにはRows（Index）と引数をわたしてあげます。このへんはCellsの仕組みと同じです。

行が取得できたらこっちのもので、あとはInsertメソッドをループでひたすら実行していくだけです。挿入後の周りのセルとのつじつま合わせはExcelがよろしくやってくれます。

こうして私が今後再びやることになるかどうかもわからない作業のためだけに完成してしまったRowInsertモジュールのソースコードは次の通りです。

Sub rowIns(Optional stepRows As Long = 1, Optional clearFormat As Boolean = False)

    Dim ws As Worksheet
    Dim startRowIndex As Long
    Dim endRowIndex As Long
    Dim insertRowCount As Long
    Dim i As Long
    Dim j As Long
    
    With Selection
        'safety
        If .Rows.Count > 1000 Then
            Exit Sub
        End If
    
        Set ws = .Parent
        startRowIndex = .Row + 1
        insertRowCount = .Rows.Count * stepRows
        endRowIndex = startRowIndex + .Rows.Count - 1 + insertRowCount
    End With
    
    For i = startRowIndex To endRowIndex Step stepRows + 1
        For j = 0 To stepRows - 1
            ws.Rows(i + j).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            If clearFormat Then
                ws.Rows(i + j).ClearFormats
            End If
        Next
    Next
    
End Sub

セルを選択した状態で実行すると、そのセルの行に対して挿入処理が行われます。セルの選択は行全体でも1つのセルでも問題ありません。プロシージャ名はrowInsとしていますが、ご自由に変えてください。

通常のSubからコールしても構いませんが、おそらく単発使用で終わりでしょうから、イミディエイトウィンドウで実行してしまうのが良いでしょう。

さいたま市区役所一覧表をサンプルとして実行してみましょう。

この状態で実行すると

このようになります。

第一引数として、stepRowsを取ります。型はLongです。省略可能でデフォルトは1です。何行挿入するかを指定できます。

この状態からstepRows=3で実行すると

このようになります。

第二引数として、clearFormatを指定できます。型はBooleanです。省略可能でデフォルトはFalseです。挿入されたRangeオブジェクトに対してClearFormatsメソッドを呼ぶかどうかのフラグで、Trueで書式をクリアします。

この状態からstepRows = 2, clearFormat = Trueで実行すると

このようになります。

ソースコードではどのような命令をしているかというと、まず選択範囲から必要な情報を取得します。

With Selection
    'safety
    If .Rows.Count > 1000 Then
        Exit Sub
    End If
    
    Set ws = .Parent
    startRowIndex = .Row + 1
    insertRowCount = .Rows.Count * stepRows
    endRowIndex = startRowIndex + .Rows.Count - 1 + insertRowCount
End With

safetyは間違って列を選択するなどで処理行が多すぎるとフリーズするので、選択セルが1000行以上で処理を中止するようにしています。

変数名を読んで字のごとくですが、開始行のインデックス、挿入する行の総数、終了行のインデックスを計算して変数に格納します。

終了行のインデックスは行が挿入されるのでずれていき、最終的に選択範囲のインデックスから挿入する行の総数分が足されたものとなります。

次にループでInsertメソッドをまわします。

For i = startRowIndex To endRowIndex Step stepRows + 1
    For j = 0 To stepRows - 1
        ws.Rows(i + j).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        If clearFormat Then
            ws.Rows(i + j).ClearFormats
        End If
    Next
Next

外のループで行の移動、内のループで挿入行数の制御をしています。挿入行の書式はInsertメソッドでは指定できないので、わざわざ挿入行に対してClearFormatsメソッドを呼ぶしかありません。

全体的に+1だったり-1だったりマジックナンバー全開なのでちょっと美しくないですが、挿入位置の調整などでこうなります。数学が得意な方はもっとショートカットできるかも知れません。私はいつも赤点ギリギリだったので。