エクセルVBAで空白行を削除しないでまとめて一括非表示で隠す方法

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

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

前回お伝えした「CountA関数でシート内の空白行を一括で削除」に続いて、今回は「空白行をまとめて一括で非表示にする方法」するテクニックをご紹介します。

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

asatte.biz

2017-07-25 20:00

前回は空白行を削除するプログラムでしたが、削除しないで非表示にしたという場合もあると思います。そこで前回のプログラムをちょこっと修正して、空白行を非表示にする機能を追加していきたいと思います。

1 空白行をまとめて非表示にするプログラムの作成
- 1.1 前回作った空白行の削除ボタンにチェックボックスを追加する
- 1.2 サンプルコードを貼り付ける
2 プログラムコードの解説
- 2.1 For文の使い方について
- 2.2 hiddenプロパティで行を非表示にする
3 空白行をまとめて非表示にするプログラムの動作確認
4 まとめ
5 時短のおつまみ
6 [連載記事全10回] エクセルVBAで文字列変換ツールを作る

空白行をまとめて非表示にするプログラムの作成

前回作った空白行の削除ボタンにチェックボックスを追加する

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

UserFrom1を開いて新たに「空白行の削除」ボタンの下に、チェックボックスとラベルを追加します。ラベルのCaptionは「非表示」とします。

チェックスボックスの追加については過去の記事を参考にしてみてください。

エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法

チェックボックスのONOFFでボタンの表示を「全角→半角」「全角→半角」とを切り替えることができれば見た目がすっきりすると思います。それに応じて一括変換の処理も変えるようにソースコードを修正していきます。

asatte.biz

2017-07-17 20:00

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

「空白行の削除」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButton4_Click()」の下に以下のコードを貼り付けます。

このプログラムコードは「CountA関数でシート内の空白行を一括で削除」で作ったコードを元にして、空白行を非表示にする処理を追加しています。

'空白行の削除
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
        
            If CheckBox3.Value = True Then
                '行を非表示
                Rows(y).Hidden = True
            Else
                '行を削除
                Rows(y).Delete
            End If
        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

If CheckBox3.Value = True Then

'行を非表示

Rows(y).Hidden = True

Else

'行を削除

Rows(y).Delete

End If

Next y

End Sub

プログラムコードの解説

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

For文の使い方について

前回の「空白行の削除プログラム」では行を削除することで、処理している行番号が変わってしまい上手く処理が行われないため、最終行から開始行に向けて処理するようにしました。

For y = Row_End To Row_Start Step -1

1	For y = Row_End To Row_Start Step -1

今回の場合は非表示なので、開始行から最終行に向けて行う通常の処理でよいのですが、削除と非表示で処理を分けるとコードが煩雑になるのであえて変更はしません。最終行から開始行に向けて処理をしても特に問題はないからです。

hiddenプロパティで行を非表示にする

row(変数).hidden = TrueまたはFalse

hiddenプロパティを使うと行または列を非表示にすることができます。Trueを指定すると非表示、Falseを指定すると表示させることができます。

非表示のチェックボックスにチェックがついているときは、空白行を非表示、チェックがついていないときは空白行を削除というように処理を分岐させています。

If CheckBox3.Value = True Then
　　'行を非表示
　　Rows(i).Hidden = True
Else
　　'行を削除
　　Rows(i).Delete
End If

If CheckBox3.Value = True Then

　　'行を非表示

　　Rows(i).Hidden = True

Else

　　'行を削除

　　Rows(i).Delete

End If

上記のコードで29行目から35行目にあたります。

コードの解説は以上です。

空白行をまとめて非表示にするプログラムの動作確認

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

ワークシートの起動ボタンを押します。

空白行を非表示にしたいシートに移動し、非表示のチェクボックスをONにします。すると「空白行の削除」ボタンが「空白行の非表示」に変わるので「空白行の非表示」ボタンを押します。

空白行である4,6,9行目が非表示になりました。

まとめ

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

チェックボックスのONOFFで行を削除するか非表示を切り分ける
hiddenプロパティで行を非表示にできる

時短のおつまみ

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

Ctrlキー + Ｈ 　置換え

検索もよく使いますが、置換えも頻繁に使う機能ですよね～。エクセルだけでなく他のアプリケーションでも使える場合が多いので覚えておくと便利ですっ。

それではまた～。

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

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