フィルタ条件の解除(単独列)

フィルタ条件を列ごとに解除するコード

'1列目のフィルタ条件を解除
Range("A1").AutoFilter 1

フィルタをかけた列の条件を列ごとに解除するには「AutoFilter」メソッドの条件を省略して実行します

1つ目の引数は指定列の数値指定になります
この数値はワークシートの列数とは一致しませんので注意してください
この点に関しては、以下の記事で解説しています

各列ごとにフィルタ条件を解除する動き
列ごとにフィルタ条件を解除

画像の動きの様にメソッドに指定列数を指定するだけで実行すると、その列のみを解除します

この動きはフィルタがかかっているかいないかは関係なく動作するのですが、逆にフィルタがかかっているものを解除する、という動きにするにはフィルタの状態を判定する必要があります

ただ、すべてをまとめて一括で条件解除をするにはShowAllDataメソッドを使用します
以下の記事で解説しています

フィルタをかける(単独列、単独条件)

最も単純な単独列の単独条件によるフィルタをかけるコード

'表範囲の1列目にフィルタをかける
Range("A1").AutoFilter 1, "=条件"

ワークシートの表範囲にフィルタの条件を指定して抽出するには「AutoFilter」メソッドを使用します

Mac版ではサポートされていないため使用できないようです
以下のページにて記載されています
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter(外部リンク)

このメソッドでは、Excel2007以降で機能がかなり追加されたため引数による柔軟な抽出がより行いやすくなりました

その中でも基本の単独列に単独の条件を指定する方法がこの例コードになります

メソッドの書式

引数(太字は必須引数)
AutoFilter Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown

今回使用する引数は2つです

「Field」はフィルタをかける表範囲の中での列数を数値で指定します
ワークシートの列数では無い点に注意が必要です

「Criteria1」はフィルタをかける際の条件になります
文字列や数値日付データなどで指定をすることが出来ます
またワイルドカードによる部分一致も可能です

Operator以下は今回は割愛します

実際の動き

フィルタをかけた時のシートデータ範囲の動き
フィルタをかける動き

画像の動きを確認してください
今回は2回フィルタをかけています

1回目が1列目に最初に「001」を含むものを指定しています
ワイルドカードによる指定が行えます
なので、「001-01,001-02,001-03」の3つが抽出されています

2回目が2列目に「対象3」を指定しています
これは1つしかデータは存在しないのでそのデータ行のみが抽出されます

条件に関して、1回目と2回目で見比べてもらうと分かる通り、最初に比較演算子を入れますが、省略も可能です
省略した場合は「=」を指定したときと同じ動きをしていることが確認できます
ですが、あまり良い使い方とはいえないかもしれませんので基本的にはちゃんと記載するようにしてください

また途中で解説したように、指定フィールド数はワークシートの列数とは一致しませんので、画像のような体裁のために空白列・行があるような場合は特に注意が必要です
画像を確認してもらうと分かりますが、1列目で変更されているのはB列なのでワークシート上では2列目になります

1列や2列であれば、簡単に判別できますが、これが数十列・数百列となってくると判別するのは困難になります
操作する表の上・左に空白行がある場合は要注意です

また気づいた方もいるかもしれませんが、このメソッドオブジェクトがほぼ意味はありません
表範囲に無いA1セルに対してメソッドを実行しているのに全く問題ありません
もちろんこんな書き方は推奨しません

このメソッドをマクロの記録によりコード作成すると表範囲が指定されています
これを行う必要はありません、そもそもこのメソッド自体がRangeに対してのメソッドなのも不思議なのですが、フィルタ機能はワークシートにつき1つしか作成されません

つまり、このセルを含むワークシートに対してフィルタを実行しているので問題が発生していないわけです
しかし全くの無意味でもありません、フィルタの矢印を設定する範囲を指定することが出来るからです

なので、それを設定する段階ではない画像のような場面ではまったく問題が起きていないのです
問題ないからそれでいいや、とは言わずにせめて表範囲の始点セルくらいは指定するようにしてください

シートのフィルタ設定を取得する

フィルタの適用状態を取得するプロパティ、使用時に注意点があります

'アクティブシートのフィルタ設定状態を取得
Debug.Print ActiveSheet.AutoFilterMode

シートにフィルタが設定適用されているかどうかを調べるには「AutoFilterMode」プロパティを使用します
このプロパティがTrueを返すと適用されており、Falseが返されると適用されていない状態となります

プロパティで設定状態を取得しているときの動き
プロパティ取得の動き

基本的には、フィルタの適用を解除したい場合に解除する前に適用されているかを調べる時に使用します

ちなみに、このプロパティでは設定を行うことも可能ですがFalseだけです
Trueを設定すると実行時エラーが発生します
Falseを代入するとフィルタの適用を解除できます
なので、こちらで解除を行えば十分に感じるかもしれませんがExcelでよく使用されるテーブル使用時に注意点が出てきます

テーブル使用時の注意点

このプロパティはWorksheetオブジェクトのメンバーです
対してテーブルはListObjectというオブジェクトになります
そのため、このプロパティが無効となってしまいます

テーブル使用時にうまくプロパティが取得できていない時の動き
テーブル時の動き

画像を確認してください
この画像ではコードもデータ内容も全く同じものを使用しています

シートのデータ範囲がテーブルに変換されている点が違います

ですが、こちらではフィルタの矢印が表示されていても、いなくてもFalseが返されています
これがこのプロパティでの大きな注意点です

フィルタをかけるメソッド自体は、テーブルかどうかに関わらず使用できます
なので非適用の状態からフィルタを適用する状態にすることは出来ています

ですが、このプロパティは無効となってしまいます
そのため、上記で解説したFalseを設定することによるフィルタの適用解除も使用できません

フィルタの操作自体はテーブルに関わらないので、問題になることはありませんが
AutoFilterModeプロパティ使用時はテーブルの存在にも注意してください

フィルタを解除する

フィルタを解除するコード、解除時には判定を行います

'フィルタが適用されていれば解除する
If ActiveSheet.AutoFilterMode = True Then
Cells.AutoFilter
Else: End If

ワークシートのフィルタを解除するには「AutoFilter」メソッドを引数を全て省略して使用します

ただこのメソッドはプロパティでは無いため、設定の適用・解除という操作が行えず、実行するたびに適用・解除が切り替わります
そのため、このメソッドを使用して解除する場合は事前にフィルタが適用されているかを調べておく必要があります

これを調べてから実行しないと、解除したいのに元々未適用であれば、適用状態になってしまいます

その適用の状態を取得するのがAutoFilterModeプロパティで、こちらはシートのプロパティになります
このプロパティについては以下の記事で解説しています

フィルタの適用設定に関しては、フィルタを指定する際にもこのメソッドを使用するので、未適用状態でフィルタを実行すれば、フィルタの適用と同時にフィルタもかかるので適用する場合にはあまり適用状態を気にする必要はありません
解除する場合のみ例コードの様に判定を行ってください

ちなみに、フィルタを解除するとかかっているフィルタも全て解除されるので、非表示になっているデータは全て表示されます

フィルタ後の結果件数を取得

SUBTOTAL関数を使用してフィルタの抽出結果件数を取得するコード

'フィルタ結果件数取得
Debug.Print Application.WorksheetFunction.Subtotal(103, Range("A:A")) - 1
フィルタの結果件数をイミディエイトに出力する
フィルタ抽出後にコードを実行

ワークシート上でフィルタ機能を使用して、抽出されたデータの件数を取得します
このコードを実行すると、件数を整数値として取得出来ます

そもそもフィルタとは、Excelの標準機能の1つです
特定の列のデータの中から指定の条件に一致するデータだけを表示させて、一致しないものは行が非表示になります

そして上記にもあるように、これは標準機能です
なのに、結果の件数を取得するプロパティやメソッドはありません
自分で数える必要があります

フィルタをかけた結果の状態
フィルタをかけた状態

この画像はフィルタをかけた状態です
画像下部の赤く色の付いた箇所にフィルタの結果件数が表示されています
この数字が欲しい訳です

ここにあるんやから、プロパティとかで簡単に取得出来そうな感じがするのに何でかありません

件数の求め方は、2通りあります
1つは記事コードのワークシート関数を使用する方法
もう1つは表示・非表示のセル個数を数えて求める方法です

結果は当然同じになるので、どっちでもええと思います
ただ後者の方法は回りクドいコードになってしまうので、ワークシート関数を使用する方が簡単です

コード解説

Debug.Print Application.WorksheetFunction.Subtotal(103, Range("A:A")) - 1

コード解説というか、これは関数解説にあたるかもしれません

ただ単にワークシート関数のSUBTOTAL関数を使用しているだけです
なので、この関数について少し解説をします

この関数は、小計関数とも呼ばれている
小計計算に適した関数です

さらに特殊なのが、引数1つ目の設定で色々な計算方法を選択することが出来る点です

今回はその中で、COUNTA関数の計算方式を使用します
COUNTA関数は空白ではないセル個数を取得する関数です

その指定箇所が、引数1つ目の103という数値の部分です
この103は、COUNTA関数の計算方法で、かつ非表示行を計算に含まない指定になります

これで、表示されたデータのあるセルの個数が取得されます

~~ - 1

ここで取得したデータのあるセルというのは、当然見出し部分も含んでいます
なので、この取得したセルの行数から見出し行の数だけマイナスすればフィルタの結果件数を取得することが出来ます

見出しの行数が2行ある場合は、「-1」を「-2」に変更するようにしてください

ただこれだけで良いのですが、プロパティがあったらええのになぁ、と感じます

何故なら、このフィルタをかけて何らかの処理を行う場合にはほぼ必須のコードだからです
フィルタの結果件数が0になる可能性は自動処理では往々にしてあります

この状態で抽出されたデータをコピペする場合、なにも範囲指定されないだけならまだしも、使用するプロパティ次第では見出し方向に指定されて見出しがコピペされてしまったりします

そもそも条件に一致するものが無いということ自体をユーザーに知らせる必要がある場合もありますので、フィルタをVBAでかける際はきっちり判定を行うようにしましょう

引数の「103」について

引数1つ目は計算方法の指定と解説しました、103という数値が表示された行のCOUNTA関数になります
これを「3」と設定した場合は、表示・非表示は考慮しなくなりますので、非表示の行のデータの個数も数えられます

関数のリファレンスにも記載はあるのですが、このSUBTOTAL関数でどちらを設定しても、フィルタにより非表示になっている行は除外されます

なので、フィルタ件数のカウントに関してはどちらでも構わない訳です
ですが記事コードでは非表示を含まないように指定しています

フィルタを使用せずに、ユーザーが非表示にした行数を数えたいことも過去にあったのでどちらにも対応できるようにしたかったからです

フィルタがかかっていない状態で、単にユーザーが行の非表示をした場合でこの「3」か「103」かで差が発生します

フィルタをかけずに、ユーザーが2行非表示にした場合にこの設定次第でその2行の差がカウント数に出ます
当然と言えば、当然ですよね
非表示を含むか含まないかの設定なんだから

まあ、そのユーザー操作の方も非表示を除外したい場合は「103」にするわけです

ただ、ここで1つ仕様上の注意点があります
そしてこれこそが記事コードの弱点でもあります

手動非表示行に対応できていない動き
手動で非表示行を設定している場合

この画像を確認してください
特に右下のレコード数の表示の部分に注目してください

「5レコード中4個が見つかり!」と、途中で切れてまるでアニメの萌えキャラの独特な語尾のようになっていますが、それはさておき実際にシート上に表示されているデータ行数と一致していません

これは「フィルタをかけたうえで手動で1行非表示にする」というとんでもなく意地悪な操作を行っているためです
それは手動で行わず、フィルタで行ってくれよ!と言いたいところですが、こんな意地悪な状況でも関数はうまく答えています

データ個数「3」が出力されています
そして、今回は引数1つ目を「3」と「103」の両方を使用していますが、どちらも同じ数値を取得しています

と、ここで不思議な話です
フィルタをかけて非表示になった行に関しては、設定の違いに関係なく非表示行は無視される、と上記でも解説しました

ですが、今回はフィルタではなく手動による非表示行があります
実際、ステータスバーの文字列のカウントが4個となっていることからもフィルタとしての非表示と違うのが分かります

これで分かるのは、ステータスバーでの個数の数え方が根本的に表示だとか非表示だとかではなく、条件に一致するものを数えているのだということです

つまり、このステータスバーの数値が簡単にプロパティで取得できればええのに~と言いましたがこの数値がそのまま表示されたデータ個数にはならないことがあるため、プロパティが無いのかもしれません

開発者の方々のデバッグ力の凄まじさが良く分かるし
やっぱりVBAって、良く考えられてる言語やな~

フィルタ機能(全解除)

フィルタの条件を全解除するにはShowAllDataメソッドを使用します

'フィルタの条件設定をクリアする
ActiveSheet.ShowAllData
'フィルタの条件設定をクリアする
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

フィルタのどれかの列でソートがかかっている状態を一括で全解除するには「ShowAllData」メソッドを使用します

このメソッドは、フィルタがかかっていない状態で実行するとエラーが発生します
なので、処理の最初に初期化しておきたいような場合はエラーを無視して実行します

フィルターが設定されているかは調べればわかりますが、あまり意味は無いと思います

また、ここでいう解除とはフィルタ機能の解除ではありません
ソート条件の解除になりますので、注意してください

フィルタを処理中にかけなおす場合は、当然エラーにはなりませんので1つ目のエラー無視は入れずに実行するといいです

ユーザーがフィルタをかけて保存していると、当然処理がうまくいかない可能性がありますのでフィルタのあるシートを処理する場合は最初に入れておくと安心です

ちなみに、昇順降順の矢印の表示もクリアされますがその設定は解除されたりはしません

Worksheets(1).ShowAllData

またこのメソッドはシートに対して行いますので、このコードのようにワークシートを指定して、別のシートのフィルタ条件を解除することもできます