エクセルVBAで集計や並び替えを使わず1行おきに空白行を一括で挿入する方法

こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も業務効率化のアイデアを発信していきますっ！

全10回の連載でエクセルのデータ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。今回はその第9回目です。

前回お伝えした「条件付き書式を使わず1行おきに色を付ける方法」に続いて、今回は「集計や並び替えを使わず1行おきに空白行を挿入」するテクニックをご紹介します。

データベースや他のシステムと連携するときに、エクセルの中にある空白行が邪魔になることがあります。空白行が入っていることで集計が上手くできなかったりすることもあります。この厄介な空白行をCountA関数で一括で削除するテクニックを紹介します。

asatte.biz

2017-07-25 20:00

エクセルシート上に入力されたデータの見栄えを整えたり、一度入力したデータに後から補足説明を入れたいときなど、1行おきに空白行を入れたいケースがあると思います。

空白行を1行おきに入れる方法は、Ctrlキーで各行を選択して右クリックで「挿入」というやりかたや、連番を振ったあとに並び替えを行う方法など・・・色々あります。

ただ前回の1行おきに色をつける方法もそうですが、数十行ならまだしも数百行になったらとてもやってられませんよね。

そこでいつものように複数人での作業も考えつつ、エクセルVBAでプログラムを作っちゃいましょう！ボタン1発でできれば作業を効率化できるし間違いも少ないですからね。

1 1行おきに空白行を挿入するプログラムの作成
- 1.1 前回使用した一括変換プログラムにボタンを追加する
- 1.2 サンプルコードを貼り付ける
2 プログラムコードの解説
- 2.1 最終行から開始行に向かって処理を実行させる
  - 2.1.1 行番号がずれる問題の解説
- 2.2 Insertメソッドで空白行を挿入する
3 1行おきに空白行を入れるプログラムの動作確認
4 まとめ
5 時短のおつまみ
6 [連載記事全10回] エクセルVBAで文字列変換ツールを作る

1行おきに空白行を挿入するプログラムの作成

前回使用した一括変換プログラムにボタンを追加する

毎度のことですが作業効率を考え、前回使ったプログラムを再利用します。デスクトップにある「一括変換.xls」を開きます。

UserFrom1を開いて新たに「1行おきに空白行を入れる」ボタンを追加します。ボタンの表示名はプロパティウィンドウのCaptionで変更できます。ユーザーフォームやボタンの大きさは見やすいように調整して下さい。

さらにボタンが増えましたね～。ずいぶん縦長になってきました。

サンプルコードを貼り付ける

「1行おきに色をつける」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButton6_Click()」の下に以下のコードを貼り付けます。このプログラムコードは「1行おきに色をつける」で作ったコードをコピーして一部を修正して使っています。

'1行おきに空白行を入れる
Private Sub CommandButton6_Click()

Dim tmp
Dim Col As Long
Dim Row_Start As Long
Dim Row_End As Long
Dim y As Long
   
    '最終行を取得
    With ActiveSheet.UsedRange
     Row_End = .Rows(.Rows.Count).Row
    End With

    'キャンセルが押された場合は処理を終了する
    On Error GoTo myError
    '処理開始行
    Row_Start = InputBox("開始行を数字で入力してください")
    On Error GoTo 0    
                       
    For y = Row_End To Row_Start Step -1
        '空白行を挿入して下方向に移動
        Rows(y).Insert Shift:=xlDown
    Next y
    
myError:

End Sub

'1行おきに空白行を入れる

Private Sub CommandButton6_Click()

Dim tmp

Dim Col As Long

Dim Row_Start As Long

Dim Row_End As Long

Dim y As Long

'最終行を取得

With ActiveSheet.UsedRange

Row_End = .Rows(.Rows.Count).Row

End With

'キャンセルが押された場合は処理を終了する

On Error GoTo myError

'処理開始行

Row_Start = InputBox("開始行を数字で入力してください")

On Error GoTo 0

For y = Row_End To Row_Start Step -1

'空白行を挿入して下方向に移動

Rows(y).Insert Shift:=xlDown

Next y

myError:

End Sub

プログラムコードの解説

重要なポイントに要点を絞って説明していきます。今回の「1行おきに空白行を入れるプログラム」はワークシート全体に対して処理をするプログラムです。前回と同じように、列を指定するInputboxは必要ないのでコードから削除しています。

最終行から開始行に向かって処理を実行させる

連続して行や列を挿入するときに注意しなければいけないことがあります。それは空白行を挿入することで、プログラムが処理している行番号がずれていくということです。

そこでFor文の中でstepを指定して下の行から上の行に向けて処理させます。

このことは「CountA関数でシート内の空白行をまとめて一括で削除する方法」で紹介しました。そのときは複数の行を削除するときは行番号がずれるため、最終行から開始行に向かって処理をさせるというものでした。

エクセルVBAのCountA関数でシート内の空白行をまとめて一括で削除する方法

asatte.biz

2017-07-25 20:00

上記のコードでは22～25行目名にあたります。

For y = Row_End To Row_Start Step -1
   '空白行を挿入して下方向に移動
   Rows(y).Insert xlShiftDown
Next

For y = Row_End To Row_Start Step -1

'空白行を挿入して下方向に移動

Rows(y).Insert xlShiftDown

行番号がずれる問題の解説

今回のプログラムに合わせて少し詳しく説明しますね。まずは下の図を見てください。3行目を処理開始行とした場合を例にします。

まず3行目の処理です。3行目に空白行が挿入され、３行目以降が下方向にシフトします。

続いて4行目の処理に移ります。4行目に空白行が挿入され、4行目以降が下方向にシフトします。つまり行を挿入するたびに下方向に行がずれていくため、ずっとA社の売上げ行の前に空白行を挿入していることになるんです。

最終行から開始行に向かって処理をしていけばこのようなことは起こらず、1行おきに空白行を入れられるというわけです。

Insertメソッドで空白行を挿入する

次のようにしてInsertメソッドを使うことで空白行を挿入することができます。

Rangeオブジェクト.Insert (Shift)

Rangeオブジェクトには行や列を指定します。ここでは行であるRowを指定していますが、列の挿入をしたい場合はColumnを指定します。

引数であるShiftはシフト方向を表す定数を指定しますが、省略可能です。

xlShiftToRight 右方向にシフト
xlShiftDown 下方向にシフト

行の挿入の場合は必ず下方向にシフト、列の挿入は右方向にシフトします。

プログラムコードの解説は以上です。

1行おきに空白行を入れるプログラムの動作確認

それでは実際に出来上がったプログラムを動かしてみましょう。

ワークシートの起動ボタンを押します。フォームが表示されたら「1行おきに空白行を入れる」ボタンを押します。

空白行を入れたいワークシートに移動し「1行おきに空白行を入れる」ボタンをクリックします。

処理を開始したい行を入力します

1行おきに空白行が入りました

まとめ

いかがでしたか？今回の内容をまとめます。

Insertメソッドで空白行を挿入できる
連続して空白行を挿入すると行番号がずれる
For文でStepを指定して最終行から開始行に向かって処理すれば行番号はずれない

時短のおつまみ

今日のおつまみはコレです！テキストエディタなどで文章を書いているときに行頭に瞬間移動できる便利なボタンです。マウスでクリックするより少～しだけ早いですよっ。エクセルの場合は先頭列のセルに移動します。

Homeキー

それではまた～。

[連載記事全10回] エクセルVBAで文字列変換ツールを作る

全角半角の変換をしたり、空白行や改行を削除したり。データ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。1つの記事で1機能ずつ追加していきますので、少しずつVBAを学びながら進めることができます。