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

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

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

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

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

asatte.biz

2017-07-21 20:00

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

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

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

1 複数人の運用を考えるとVBAを活用したほうが効率が良い
2 シート内の空白行を一括で削除するプログラムの作成
- 2.1 前回使用した一括変換プログラムにボタンを追加する
- 2.2 サンプルコードを貼り付ける
3 プログラムコードの解説
4 空白行の削除プログラムの動作確認
5 まとめ
6 時短のおつまみ
7 [連載記事全10回] エクセルVBAで文字列変換ツールを作る

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

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

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

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

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

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

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

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

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

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

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

エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法

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

asatte.biz

2017-06-24 08:36

'空白行の削除
Private Sub CommandButton4_Click()

Dim tmp
Dim Col As Long
Dim Row_Start As Long
Dim Row_End As Long
Dim y As Long

    '処理開始行
    Row_Start = 2
    
    '最終行を取得
    With ActiveSheet.UsedRange
     Row_End = .Rows(.Rows.Count).Row
    End With
    
     For y = Row_End To Row_Start Step -1
        
        'CountAを使って行のデータの個数をカウント
        If Application.WorksheetFunction.CountA(Rows(y)) = 0 Then
           '行を削除
           Rows(y).Delete           
        End If
    Next y    

End Sub

'空白行の削除

Private Sub CommandButton4_Click()

Dim tmp

Dim Col As Long

Dim Row_Start As Long

Dim Row_End As Long

Dim y As Long

'処理開始行

Row_Start = 2

'最終行を取得

With ActiveSheet.UsedRange

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

End With

For y = Row_End To Row_Start Step -1

'CountAを使って行のデータの個数をカウント

If Application.WorksheetFunction.CountA(Rows(y)) = 0 Then

'行を削除

Rows(y).Delete

End If

Next y

End Sub

プログラムコードの解説

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

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

Application.WorksheetFunction.関数名

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

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

ということでしょうか。

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

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

CountA(範囲）

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

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

If Application.WorksheetFunction.CountA(Rows(y)) = 0 Then
　　　'行を削除
　　　Rows(y).Delete
End If

If Application.WorksheetFunction.CountA(Rows(y)) = 0 Then

　　　'行を削除

　　　Rows(y).Delete

End If

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

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

For y = Row_End To Row_Start Step -1

1	For y = Row_End To Row_Start Step -1

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

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