'フィルタ結果件数取得
Debug.Print Application.WorksheetFunction.Subtotal(103, Range("A:A")) - 1
ワークシート上でフィルタ機能を使用して、抽出されたデータの件数を取得します
このコードを実行すると、件数を整数値として取得出来ます
そもそもフィルタとは、Excelの標準機能の１つです
特定の列のデータの中から指定の条件に一致するデータだけを表示させて、一致しないものは行が非表示になります
そして上記にもあるように、これは標準機能です
なのに、結果の件数を取得するプロパティやメソッドはありません
自分で数える必要があります
この画像はフィルタをかけた状態です
画像下部の赤く色の付いた箇所にフィルタの結果件数が表示されています
この数字が欲しい訳です
ここにあるんやから、プロパティとかで簡単に取得出来そうな感じがするのに何でかありません
件数の求め方は、２通りあります
１つは記事コードのワークシート関数を使用する方法
もう１つは表示・非表示のセル個数を数えて求める方法です
結果は当然同じになるので、どっちでもええと思います
ただ後者の方法は回りクドいコードになってしまうので、ワークシート関数を使用する方が簡単です
コード解説
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って、良く考えられてる言語やな～