【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでバラバラの経費精算書のデータを収集するマクロの作り方をお伝えしています。

前回の記事はコチラ。

【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法

エクセルVBAでバラバラの経費精算書ファイルのデータを収集するマクロの作り方をお伝えしています。今回はエクセルVBAでエラーが発生したときにメッセージを表示する処理を追加する方法をお伝えしていきます。

tonari-it.com

2018.06.29

マクロの実行時にエラーが発生したときに、処理を分岐する方法についてお伝えしました。

今回は、ちょっと別方面の「エラー」について対策をしていきます。

その「別方面」というのは、オートフィルタも含めて行の非表示をしているときに起こるエラーです。

ということで、今回はエクセルVBAでオートフィルタや行の非表示で隠れている行を全部表示する方法です。

では、行ってみましょう！

前回のおさらい

前回までに作成したマクロはコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
            
            Dim period As String: period = Left(f.Name, 6)
            Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
                                    
            On Error Resume Next
            Dim staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
            Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
            Dim departmentId As Long: departmentId = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
            Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)

            If Err.Number <> 0 Then
                MsgBox f.Name & " の処理でエラーが発生しました。"
                staffId = 0
                staffName = ""
                departmentId = 0
                departmentName = ""
            End If
            On Error GoTo 0
                      
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = departmentId  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

前回は21～28行目。実行時エラーが起きたときに、ErrオブジェクトのNumberプロパティを見て、エラーが発生しているかどうかを判定。それにより分岐処理をするという部分を作成しました。

しかし、このマクロでは特定の条件では、うまく動作しません。

以下説明をしていきますね。

行の非表示でマクロが正しく動かない

このコードでは以下のようなケースで不具合が起こることがあります。

まず、経費精算書のデータを集めるシートである「経費データ」シートには、既に2018年7月のデータが以下のように入力されている状態とします。

2行目から8行目まで、全7件のデータが記録されていますね。

ただ、色々と作業をする上で、以下のようにオートフィルタなどを使用して、4～8行目が非表示になっているとします。

このように、一部の行が非表示になっている状態で、2018年8月の経費精算書データを追加で収集します。

すると…おや？9行目以降に経費データが追加されるはずなのですが、何も追加されていないようです。

フィルタを外して、行を再表示してみると、以下のように4行目から6行目に、上書きでデータが追加されてしまいました。

これは明らかに問題です。前のデータが上書き、つまり消えてしまうなんて困りますもんね。

行の非表示でデータが上書きされてしまう理由

この原因はコードの6行目の以下の部分です。

di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1

このステートメントは、シートのA列の1番下からショートカットキー Ctrl + ↑ をしたときの行番号にプラス1をした数値を返します。

もうお察しだと思いますが、例えば行の非表示をした状態で、実際に Ctrl + ↑ をしてみてください。

以下のように、3行目にカーソルがいってしまいます。

ですから、diには「4」が入り、4行目からデータの入力がはじまってしまったのです。

このように、行が非表示になっている場合は、マクロの動作に影響をしてしまうことがあります。

運用上、ワークシートで行の非表示をするようなする操作をする可能性がある場合は、対策をうっておくと良いでしょうか。

隠れている行を表示する

この問題を解決する方法はいくつかあります。

今回は、最もシンプルな方法で、隠れている行を表示してあげるという方法です。

それで、非表示になっているケースも、オートフィルタで非表示になっている場合と、行の非表示で非表示になっている場合では、解除の方法が違うので、両方やっておきます。

オートフィルタを解除する方法

オートフィルタを解除するには、WorksheetオブジェクトのAutoFilterModeプロパティの値を設定してあげます。

AutoFilterModeプロパティは、指定したワークシートにオートフィルタが設定されているかどうかをBoolean型で表します。

ですから、これをFalseにしてあげれば、オートフィルタが解除され、それに伴ってオートフィルタで非表示になっている行も表示されるということになります。

つまり、以下のステートメントを冒頭などに入れてあげればOKです。

wsData.AutoFilterMode = False

なお、AutoFilterModeプロパティはTrueに設定することはできません！

適用をするには、AutoFilterメソッドを使います。ご興味あれば、調べてみてください。

行の非表示を解除する方法

行の非表示で隠されているのであれば、それを解除するための別のプロパティを使います。

Hiddenプロパティというものです。

書き方としては以下の通りです。

ここで対象となるRangeオブジェクトは行全体、または列全体を表すRangeオブジェクトである必要があります。

対象のRangeオブジェクトが非表示ならTrue、非表示でないならFalseとなります。

今回は、全ての行について非表示を解除したいので、以下のステートメントを冒頭などに入れてあげればOKですね。

wsData.Rows.Hidden = False

もし、列の非表示も気になるのであれば、以下も入れてしまうという手もあります。

wsData.Columns.Hidden = False

まとめ

以上、エクセルVBAでオートフィルタや行の非表示で隠れている行を全て表示する方法についてお伝えしました。

エクセルシートについて、オートフィルタや行（または列）の非表示をするような操作の可能性があるのであれば、念の為入れておくと良いですね。

まとめのコードはコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

With wsData
    .AutoFilterMode = False
    .Rows.Hidden = False
    Dim di As Long: di = .Cells(Rows.Count, 1).End(xlUp).Row + 1
End With

Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
            
            Dim period As String: period = Left(f.Name, 6)
            Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
                                    
            On Error Resume Next
            Dim staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
            Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
            Dim departmentId As Long: departmentId = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
            Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)

            If Err.Number <> 0 Then
                MsgBox f.Name & " の処理でエラーが発生しました。"
                staffId = 0
                staffName = ""
                departmentId = 0
                departmentName = ""
            End If
            On Error GoTo 0
                      
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = departmentId  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

次回は、今回の問題を別の切り口から解決してみます。

どうぞお楽しみに！

連載目次：エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。

1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
2. 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
5. 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
7. 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する