VBAオートフィルターで抽出したレコード件数のカウントを、コピペして使えるユーティリティ関数にしたものを紹介します。お忙しい方は、ソースコードをコピーして、使い方のように呼び出すだけでOKです。コードをしっかり読みたい方は、解説をどうぞ。最初のIf部分は例外処理なので、ひとまず置いておきます。絞り込み件数をカウントしている、メイン処理の解説から。レコードが抽出されているかを知るには、その行が表示されているかを調べます。どこか1列の見えているセルの数を数えることで計算できます。1日本語訳を読めば、計算の流れは大体つかめると思います。細かい解説は↓の通り。.AutoFilter.Rangeオートフィルターがかかっている表全体(▼のついている見出しの行を含む)を、Rangeオブジェクトで取得します。「Rangeオブジェクト.Columns(1)」だと、範囲内の第1列部分を取得できます。Range("B1:D10").Columns(1)は、この範囲の1列目である、Range("B1:B10")を返します。.SpecialCells(xlCellTypeVisible)見えているセル(可視セル)をRangeオブジェクトで取得します。行列の表示設定と、オートフィルターでの表示状況が、どちらも反映されます。1.Countで見えているセルの数を数え、見出し行(▼のついている行)の分を1引いて計算終了です。オートフィルターが空っぽのデータに設置されているときは注意!マクロを実際の業務で使っていると、いろいろな表に出くわします。中には、空のデータ(見出ししかないデータ)が混じることもあるでしょう。みかんを探す以前に、そもそもくだものが一つも売れなかった日があるかもしれません。このとき、この関数は0を返すのが自然かと思われますが、さてこの時、上のメインの処理がどうなっているかを追ってみましょう。0」になってるから、Ifの条件分岐はいらないように見えますね。残念ながら、とんでもない罠が仕掛けられていて、↑の計算の通りになりません。Range("A1")こいつが罠です。「A1の中で見えているセル=A1」と思いたいですが、なんとこれは「シート全体で見えているセルすべて」に変換されます。そのせいで、抽出中のレコード数に、0ではなくて532とか謎の数字が返ってきてバグります。原因は、「SpecialCellsを単独のセルから呼び出した場合は、シート全体から呼び出されたものとみなす」というトラップです。いつかとんでもない地雷を踏む可能性があるので、SpecialCellsを使うならこの記事も読んでおくことをおすすめします。www.limecode.jpもう一つの方法:SUBTOTAL関数を使うオートフィルターで抽出中の件数を数えるやりかたとして、ワークシート関数の「SUBTOTAL関数」を使う方法もあります。Range("B2:B10"))これでも抽出件数を求めることができます。SUBTOTAL関数は、オートフィルターでの抽出が反映されるさまざまな集計を行える関数です。↑の例における「3」の部分でやりたい集計を選択します。「3」はCOUNTAなので、今回やりたい「件数のカウント」が行えます。「9」のSUM=合計や、「4,5」のMAX,MINなども便利ですね。抽出データの合計や、抽出した中での最大値などを求めることができます。SUBTOTALとSpecialCellsの違いさて、「SUBTOTALのCOUNTA」と「SpecialCellsの可視セル」の比較ですが、とても分かりやすく、お互い短所があります。・SUBTOTALまず、SUBTOTALさんですが、「抽出中のCOUNTA」という説明を聞いて、怪しさを感じ取った方もいると思います。SUBTOTALさんは「COUNTA=データのあるセルを数える」ことしかできません。よって、空白セルのある列では使うことができません。データが埋まっている列を選んで実行する必要があります。・SpecialCells次はSpecialCellsさんですが、こちらは「表示されているセル」を数えるため、手動で非表示にした行や列が反映されてしまいます。行を非表示にすると、レコードも減って計算されますし、フィルターの一番左の列を非表示にすると、↑のユーティリティ関数は常に「0」を返すようになってしまいます。使い分けるのが良いか↑で挙げた両方の短所の「発生率」や「発生して仕方ないか」を考えましょう。「データの中に空白セルが混じる」のは普通です。別に変な使い方ではありません。「フィルター付データを、さらに手で非表示にする」はちょっと変な使い方です。そもそも好ましい操作ではなく、件数以外の問題も起きそうです。「途中の列を非表示すること」は変な使い方ではないですが、この操作では↑の関数は正常に動きます。左の列でのみ問題が起こります。見ると、「Excelを正しく使ったときにエラーの起きにくい」SpecialCellsさんがよさそうですね。ユーティリティ関数はSpecialCellsで作りました。敗れてしまったSUBTOTALさんですが、こちらにも大事な役目があります。それはもちろん、セルに関数を入れるときです。「○件を抽出中」や「現在の合計:○」などをセルに表示させて、ユーザーの操作をリアルタイムで結果に反映させるのは、シート関数のお役目です。マクロは「実行」するには便利ですが、「自動計算」には不向きですからね。いうことで、結論は「マクロに組むならSpecialCells」「セルに打ち込むならSUBTOTAL」です。使い分けていきましょう。SUBTOTALをセルに入れるときは、「空白のない列」を参照するのを忘れずに。Functionこの記事を書くために、自分のユーティリティ関数を見たらこうなってました。2列分の可視セルを数え、そのあと2で割ることで、単独セルになる罠を回避しています。~とは思いますが、こういうパズル的な書き方はやってはいけません。コードはとにかく読みやすさが大事です。後で見返してコードの意味を30秒考えたら、そのプログラムは遅いプログラムです。このブログを書くために書き直す羽目になったので、とても遅いプログラムだったといえるでしょう。23:00Tweet関連記事2022-12-25セル範囲や複数セルから1次元配列を生成するセル範囲などRangeオブジェクトを格納した1次元配列を生成する...2022-12-24複数のセル値を区切り付き文字列に結合する複数のセルの値を文字列に結合する方法を解説します。変数にmyRngとかmyNumってよく見ますよね。「日本語の変数でプログラミングすれば、みんなが幸せになれる」という思想の元、主にExcelVBAのマクロやコードを解説しているブログです。この思想については↓の記事をご覧ください。変数を日本語にすればプログラミングが上達する記事の更新通知や質問の回答はTwitterでも行っております。お気軽にフォローください。12はてなブログをはじめよう!LimeCodeさんは、はてなブログを使っています。ブログを報告する引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。