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

自力で老眼回復

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

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

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

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

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

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

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

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

レクタングル大336×280

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

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

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

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

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

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

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

プログラムコードの解説

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

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

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

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

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

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

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

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

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

まず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を学びながら進めることができます。

  1. エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法
  2. エクセルVBAでセル内の文字列を操作して全角文字を半角に一括変換する方法
  3. エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法
  4. エクセルVBAでセル内文字列の前後の空白をまとめて一括で削除する方法
  5. エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する
  6. エクセルVBAのCountA関数でシート内の空白行をまとめて一括で削除する方法
  7. エクセルVBAで空白行を削除しないでまとめて一括非表示で隠す方法
  8. エクセルVBAで条件付き書式を使わず1行おきに色を付ける方法
  9. エクセルVBAで集計や並び替えを使わず1行おきに空白行を一括で挿入する方法 (本記事です)
  10. エクセルVBAで同じデータが切り替わる項目ごとに空白行を挿入する方法
レクタングル大336×280
レクタングル大336×280

シェアする

  • このエントリーをはてなブックマークに追加

フォローする