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

自力で老眼回復

こんにちは~。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も「時間クリエーター」としてバシバシ時短に取り組んでます!

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

前回お伝えした「Replace関数でセルの文字列内の空白を一括削除する」に続いて、今回は「CountA関数でシート内の空白行を一括で削除」するテクニックをご紹介します。

エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する
Replace関数を使うと文字列の置換を行うことができます。Trim関数では文字列の前後の空白を削除することはできても、文字列内にある空白は削除できません。置換するわけではなく削除したい場合、置換後の文字列を何も指定しないで""とすることで、検索文字列を削除したことになります。

ワークシート内のデータが入っていない空白行。表の区切りなどで見栄えを良くすることが目的で使うことがあると思います。

しかしこの空白行が邪魔になることがあるんですよね~。空白行が入っていることで集計が上手くできなかったり、外部システムと連携するときに困るケースがあります。

データベースと連携するときにCSV形式などのテキストファイルに書き出す場合があると思いますが、このテキストファイルに無意味な空白行が入ってしまいます。そのことでデータベースに取り込み時に意図しない動きになったりすることがあります。

レクタングル大336×280

複数人の運用を考えるとVBAを活用したほうが効率が良い

そんなわけで無意味な空白行は削除したいですよね。空白行の削除なんですが、オートフィルタを使って空白セルを絞り込んで削除する方法や、「ジャンプ」という機能を使って空白セルを選択して削除する方法などがあります。

しかしこの方法ですが、数ステップの工程を踏まなければならないし、その工程を覚える必要があります。覚えてしまえばどうってことないですが、複数人で行う作業の場合、全ての人に教えて覚えてもらうのは結構なパワーと労力が必要になってきます。

そこでボタン一発でできるVBAを使った方法をご紹介します。前回と同じように別のエクセルファイルでも使える汎用的なプログラムを作っていきましょう。

シート内の空白行を一括で削除するプログラムの作成

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

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

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

それにしてもずいぶんボタンが増えてきましたね。まだ増える予定ですのでお楽しみに~。

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

「空白行の削除」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButton4_Click()」の下に以下のコードを貼り付けます。このプログラムコードは「セル内の文字列に含まれる改行を一括で削除する方法」で作ったコードをコピーして一部を修正して使っています。

エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法
ワークシートのセル内にある改行を消したいって思ったことありませんか?データを別のアプリケーションに取り込んだり、データベースに登録したりするときに改行が邪魔になることがあります。そんな時に改行を一括で削除できるツールがあったら便利ですよね。

プログラムコードの解説

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

WorksheetFunctionプロパティでワークシート関数を使用する

Application.WorksheetFunction.関数名

ApplicationオブジェクトのWorksheetFunctionプロパティを使用するとVBAでワークシート関数を呼び出すことができます。ワークシート関数を使うメリットしては、

  • VBA関数にはない関数が使える
  • 処理が早い
  • プログラムの記述がラクになる場合がある

ということでしょうか。

CountA関数で行にデータが入っているか調べる

ワークシート関数が使えるようになりましたので、関数名にCountAを指定します。

CountA(範囲)

CountA関数は指定した範囲内にいくつデータが入っているかを調べる関数です。範囲はB1:B5のようにセル範囲を指定することもできますが、今回の場合、行全体のデータを調べたいので行番号を指定しています。

今回のプログラムでは以下のようにして、行全体のデータ数を調べ、1つもなかったらその行を削除する。という処理にしています。

For文で最終行から開始行にかけて処理を繰り返す

このFor文ですが今までとちょっと違うことにお気づきでしょうか?

どのような違いがあるのか今までのものと比較してみましょう。

変更前 For y = Row_Start Step to Row_End
変更後 For y = Row_End To Row_Start Step -1

Row_EndとRow_Startが逆になっていますね。あとは変更後は文末にStep -1がついていますね。Row_Startは開始行、Row_Endは最終行ですから、

変更前 開始行から最終行に向かって処理
変更後 最終行から開始行に向かって処理

ということになります。

ここで変更後のFor文なのですが、最終行から開始行に向かっています。例えば最終行が30、開始行が2の場合、

For  i = 30 to 2

しかしこのように書いた場合、一度もループすることなくFor文から抜けてしまいます。開始が30なのですでに終了の2より大きい数字だからです。そこで

step -1

を指定することで、30から2にかけて1ずつ数字を減らしてループさせることができます。

複数行を削除するときは下から上の行に向かって行う

ところでなぜ最終行から開始行に向かってFor文を実行するのでしょうか?それは複数行の削除を行う場合、開始行から最終行に向かって行うと上手くいかないからです。

どういうことか説明しますね。まずは下の図を見てください。開始行から最終行に向かって処理をしていく例です。

2,3行目が空白行です。まず2行目を削除します。すると2行目が削除されたことで、その後の行が上へシフトアップします。するともともと3行目にあった空白行が2行目にせりあがってきます。

しかしここで2行目はすでに削除処理をしていますから、次は3行目の処理に移ります。3行目は空白行ではありません。ここでループが終了します。結果、もともと3行目にあった空白行は削除されないまま残ってしまいます

続いて最終行から開始行に向かって処理をしていく例を見てみましょう。

3行目の空白行を削除した後、3行目以降が上へシフトアップします。シフトアップして3行目に移る行はすでに処理した4行目ですから問題ありません。続いて2行目に処理が移ります。2行目は空白行なので削除します。結果、無事に空白行である2,3行目が削除されました。

このように複数行を削除する場合は後ろの行から削除していくのがポイントになります。

空白行の削除プログラムの動作確認

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

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

空白行が削除されました。

まとめ

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

  • ApplicationオブジェクトのWorksheetFunctionプロパティを使うワークシート関数を呼び出せる
  • CountA関数で行にデータが入っているか調べることができる
  • 複数行の削除は後ろの行から前の行に向かって行う

時短のおつまみ

今日のおつまみはコレです!

Ctrlキー + F  検索

検索ダイアログが表示され、検索が行えます。ほとんどのアプリケーションで共通ですので覚えておくと時短できますよっ。パソコンの作業って1に検索、2に検索ってくらい、検索の頻度が高いですからね~。

それではまた~。



[連載記事 全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

シェアする

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

フォローする