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

すでにある表に規則的に行を挿入します。

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

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

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

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

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


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

rowin1.png

内容はさいたま市の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からコールしても構いませんが、おそらく単発使用で終わりでしょうから、イミディエイトウィンドウで実行してしまうのが良いでしょう。


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

この状態で実行すると

rowin2.png

このようになります。

rowin3.png


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

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

rowin4.png

このようになります。

rowin5.png


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

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

rowin4.png

このようになります。

rowin6.png


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

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

使用方法

一応クラスファイルのダウンロードボタンを用意しますが、ソースコードをコピペしたほうが早いと思います。

クラスファイルはVBEにドラッグ&ドロップしてご利用ください。

一番の注意点として、VBAの実行結果は「やり直し」ができません。やる前にファイルを保存するなりコピーするなりしましょう。

ダウンロード

利用上のご注意
  • ダウンロードしたファイルを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • Windows10-64bit Excel2016-32bit環境でのみ動作確認済み。

RowInsert.bas

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

related pages
Excelマクロを指定した時刻に自動実行させる
定型作業は全自動でコンピューターにやらせましょう。

ある程度VBAが使えるようになるとこう思います。「もうこれボタン押すだけだし、オレがやる必要なくね?」マクロ実行命令すらもコンピューターに委ねたいですか?はい、可能です。これは我々、はたらきたくない会社員の理想を実現するために必須のテクニックになります。

Read More ...
2つのExcelブックの内容を比較する
Excelで間違い探し。

同じ構成の2つのブックでどこが違うのか知りたいならば、あなたはどうしますか? 目視は論外として、ワークシート関数やフィルターを使った方法をいくつか思いつきますが、毎回違うファイルが対象になるとしたら、手軽さや取り回しの良さは期待できないことは想像に難くないでしょう。ここはプログラムにやらせましょう。

Read More ...
Power Automate DesktopでAIが人類を支配する時代にまた一歩近づく
Microsoft純正RPAツールがついに無償公開されてしまう!

Power Automate Desktopのお手軽で強力な機能がデスクトップレコーダーでしょう。まんま名前の通りですが、あなたがPCで操作した内容をそのまま記録しておくことができます。次に同じ操作が必要になったのなら再生ボタンを押すだけであとはPower Automate Desktopが代わりにやってくれます。

Read More ...
この記事の
作成日

2019-11-03

更新日

2019-11-12

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園