VBAでオートフィルターを解除する方法|絞り込み中に最終行がずれる原因と対策【VBA入門】

Excel標準機能のオートフィルター。表計算ソフトならでは、とても便利な機能です。
が、データ絞り込みを行った状態のまま、【最終データ行】の取得は、注意が必要です。

みなさん、こんにちは。セルネッツ竹本です。
今回は、「オートフィルターON状態での最終データ行の取得」をテーマに、解説をしたいと思います。

結論から言うと、絞り込み中に End(xlUp) で最終行を取ると、隠れた行が飛ばされ、見えている最後の行が返ります。
対策は、処理の前にオートフィルターを解除する(ShowAllData)か、絞り込みに左右されない方法で行数を数えることです。

この記事では、次の内容がわかります。

• 絞り込み中に最終行がずれる理由
• VBAでオートフィルターを解除する方法と、コードの意味
• 解除せずに最終行を取る方法、見えている行だけを処理する方法

「オートフィルター絞り込み」中は正しい最終行が取得できない理由

VBA初心者あるある、の中の一つです。

最終行の取得でよく使う次のコードは、A列の一番下から上へたどり、最初にデータがあるセルの行番号を返します。

'★Fin行
P_Fin行 = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

ところが、オートフィルターで絞り込んでいると、End(xlUp)は隠れている行を飛ばして止まります。
そのため、本当の最終行ではなく「見えている最後の行」が返ってしまいます。

シートの状態	End(xlUp)で取れる行	起きること
絞り込みなし(2〜10行目にデータ)	10行目	正しく全件を処理できる
絞り込み中(8〜10行目が隠れている)	7行目	8〜10行目が処理されず、転記や集計が漏れる

エラーは出ないまま結果だけがずれるので、気付きにくいのがこの不具合の怖いところです。
最終行を求める基本の方法は、シート内データの最下行を求める2つの方法で解説しています。

Excel業務では、利用者が一般の事務系の方が多いため、日々の実務経験により、Excelの知識も豊富な場合が多いです。
このため、日頃からExcel機能を駆使して実務を行っている分、オートフィルターやSORT、ピボットテーブルなど、ワークシートに対するあらゆる操作やデータ状態がテストできないと、後に重大な障害をもたらすことがあります。
これが、高度な技術を要し、データベース接続を得意とするエンジニアスキルとは異なる、Excel開発の怖いところでもあるのです。
このため、プログラム処理では、オートフィルターOnを想定した、毎回リセットが重要になります。

シートを毎回同じ状態から始める考え方は、リセット処理でバグの発生率を下げる方法でも紹介しています。

VBAでオートフィルターを解除する方法(フィルター解除のModule)

処理の最初に次のModuleを呼び出すと、「フィルターはON、絞り込みはなし」の状態にそろえられます。

'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
Sub M_AutoFilter適用()
'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

On Error Resume Next

'★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
'★
'★　【Filter:ON】且つ【絞り込み:Off】の状態とする。
'★
'★　 (1/2)　FilterがOffなら、Onにする
'★　 (2/2)　FilterがON、且つ、絞り込みOnなら、Offにする
'★　 ※つまり、FilterがON、かつ、絞り込みOffなら、そのまま
'★
'★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆

If ActiveSheet.AutoFilterMode = False Then

    ActiveSheet.Range("A1").AutoFilter

Else

    If ActiveSheet.AutoFilter.FilterMode Then

        ActiveSheet.ShowAllData

    End If

End If

On Error GoTo 0

End Sub

コードの意味は次のとおりです。

コード	意味
ActiveSheet.AutoFilterMode	シートにオートフィルター(▼ボタン)が付いているか
Range(“A1”).AutoFilter	A1を含む表にオートフィルターを付ける
AutoFilter.FilterMode	実際に絞り込みがかかっているか
ShowAllData	絞り込みを解除して、すべての行を表示する

ShowAllDataは、絞り込みがない状態で実行するとエラーになります。
そのため、FilterModeで絞り込み中かどうかを確かめてから実行しています。
仕様は、Microsoft LearnのWorksheet.ShowAllData メソッドとAutoFilter.FilterMode プロパティで確認できます。

解除したあとで、最初に紹介した End(xlUp) で最終行を取れば、正しい行が返ります。

フィルターを解除せずに最終行を取る方法

【Point】最終行の判定をEnd(xlUp)の方法で取得した場合のリスクです。
データ有効範囲のテーブル行数、列数で取得した場合は、非表示データがあっても問題ありません。

表のまとまり(CurrentRegion)の行数は、隠れている行も含めて数えます。
見出しがA1から始まり、途中に空白行がない表なら、次のように書けます。

取り方	絞り込み中	注意点
Cells(Rows.Count, 1).End(xlUp).Row	見えている最後の行になる	先にフィルターを解除する
Range(“A1”).CurrentRegion.Rows.Count	隠れた行も含めて数える	表がA1から始まり、空白の行・列で途切れていないこと

見えている行だけを処理したいとき

反対に、絞り込んだ結果だけを転記したいときは、見えているセルだけを取り出します。

「全件を処理したいのか」「絞り込んだ分だけ処理したいのか」を先に決めておくと、どちらの書き方を使うか迷いません。

オートフィルターを扱うときの注意点

• テーブル(ListObject)のフィルターは別:テーブルに変換した表は、ListObject側のAutoFilterで解除する
• On Error Resume Nextはすぐ戻す:上のModuleのように、使ったら On Error GoTo 0 で元に戻す。戻し忘れると、ほかのエラーまで見逃す
• ActiveSheetに頼りすぎない:処理中に別のシートが選ばれていると、意図しないシートを解除してしまう。対象のシートを名前やオブジェクト名で指定すると安全
• 並べ替えやピボットテーブルの状態も確かめる:フィルター以外の操作でも、データの並びや範囲が変わることがある

On Error Resume Nextの使い方は、On Error Resume Nextを使うときの注意点で解説しています。
対象のシートを取り違えるリスクは、For Eachで全シートを操作するときに潜むリスクも参考になります。

よくある質問

Q. ShowAllDataでエラーが出るのはなぜですか?

A. 絞り込みがかかっていない状態で実行したためです。
FilterModeがTrueのときだけ実行するようにすると防げます。

Q. オートフィルターの▼ボタンごと消したいときは?

A. 「ActiveSheet.AutoFilterMode = False」と書くと、オートフィルターそのものが外れます。
絞り込みだけを解除して▼ボタンを残したいときは、ShowAllDataを使います。

Q. 手作業で非表示にした行でも最終行はずれますか?

A. この記事で取り上げたずれは、オートフィルターで絞り込んだときに起きるものです。
行の非表示やグループ化を使っている表でも、処理の前に一度すべての行を表示しておくと安心です。

まとめ

今回のテーマは、「オートフィルターON状態での最終データ行の取得」について、解説を行いました。

• 絞り込み中のEnd(xlUp)は、隠れた行を飛ばして見えている最後の行を返す
• 処理の前に、FilterModeを確かめてShowAllDataで解除する
• 解除しないなら、CurrentRegionの行数など、隠れた行も数える方法を使う

不具合の起きにくいマクロの組み立て方は、コード設計のコツを覚えて設計力をスキルアップする方法でも紹介しています。

本記事が、Excel実務の効率化において、ご参考お役立てになれば幸いです。