オートフィルターで抽出したレコード件数のカウントを、コピペして使えるユーティリティ関数にしたものを紹介します。
お忙しい方は、ソースコードをコピーして、使い方のように呼び出すだけでOKです。
コードをしっかり読みたい方は、解説をどうぞ。
ソースコード
' 抽出件数のカウント Function Countフィルター抽出中の件数(指定シート As Worksheet) As Long ' ◇ もともとデータが空のときは0(フィルター設置範囲が▼見出し1行だけのとき) If 指定シート.AutoFilter.Range.Rows.Count = 1 Then Countフィルター抽出中の件数 = 0 Exit Function End If ' オートフィルター範囲1列目の可視セル数をカウント Countフィルター抽出中の件数 = 指定シート.AutoFilter.Range.Columns(1) _ .SpecialCells(xlCellTypeVisible).Count - 1 End Function
使い方
Call フィルターで抽出する("みかん", くだもの売上表の列.品物, ActiveSheet) Call フィルターで抽出する(">=10", くだもの売上表の列.個数, ActiveSheet) If Countフィルター抽出中の件数(ActiveSheet) > 0 Then Call みかん箱を用意する End If
※ 一緒に使われている関数：オートフィルターで抽出する
※ くだもの売上表の列：列番号を定数で定義したものです（B列なら2などの数値です）
コードの解説
絞り込んだレコードを数える部分の解説
最初のIf部分は例外処理なので、ひとまず置いておきます。
まずは絞り込み件数をカウントしている、メイン処理の解説から。
レコードが抽出されているかを知るには、その行が表示されているかを調べます。
よって絞り込まれたレコードは、見えている行の数 ＝ どこか1列の見えているセルの数を数えることで計算できます。
' メインのカウント部分 指定シート.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 ' 日本語訳 指定シート.オートフィルターエリアの第1列.見えているセル.数 - 1
日本語訳を読めば、計算の流れは大体つかめると思います。細かい解説は↓の通り。
.AutoFilter.Range
オートフィルターがかかっている表全体(▼のついている見出しの行を含む)を、Rangeオブジェクトで取得します。
.Columns(1)
なじみのある「ワークシート.Columns(1)」だと列全体を取得しますが、
「Rangeオブジェクト.Columns(1)」だと、範囲内の第1列部分を取得できます。
例えばRange("B1:D10").Columns(1)は、
この範囲の1列目である、Range("B1:B10")を返します。
.SpecialCells(xlCellTypeVisible)
見えているセル(可視セル)をRangeオブジェクトで取得します。
行列の表示設定と、オートフィルターでの表示状況が、どちらも反映されます。
.Count - 1
.Countで見えているセルの数を数え、見出し行(▼のついている行)の分を1引いて計算終了です。
オートフィルターが空っぽのデータに設置されているときは注意！
マクロを実際の業務で使っていると、いろいろな表に出くわします。
中には、空のデータ（見出ししかないデータ）が混じることもあるでしょう。
みかんを探す以前に、そもそもくだものが一つも売れなかった日があるかもしれません。
このとき、この関数は0を返すのが自然かと思われますが、
さてこの時、上のメインの処理がどうなっているかを追ってみましょう。
① AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 ② Range("A1:C1").Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 ③ Range("A1").SpecialCells(xlCellTypeVisible).Count - 1 ④ Range("A1").Count - 1 ⑤ 1 - 1 = 0
一見すると「見出しだけの時 = 0」になってるから、Ifの条件分岐はいらないように見えますね。
しかし残念ながら、とんでもない罠が仕掛けられていて、↑の計算の通りになりません。
③から④に進んだときの
Range("A1").SpecialCells(xlCellTypeVisible) ⇒⇒ Range("A1")
こいつが罠です。
「A1の中で見えているセル=A1」と思いたいですが、なんとこれは「シート全体で見えているセルすべて」に変換されます。
そのせいで、抽出中のレコード数に、0ではなくて532とか謎の数字が返ってきてバグります。
原因は、「SpecialCellsを単独のセルから呼び出した場合は、シート全体から呼び出されたものとみなす」というトラップです。
いつかとんでもない地雷を踏む可能性があるので、SpecialCellsを使うならこの記事も読んでおくことをおすすめします。
もう一つの方法：SUBTOTAL関数を使う
オートフィルターで抽出中の件数を数えるやりかたとして、ワークシート関数の「SUBTOTAL関数」を使う方法もあります。
' セルに書くとき =SUBTOTAL(3,B2:B10) ' VBAで書くとき WorksheetFunction.Subtotal(3, 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をセルに入れるときは、「空白のない列」を参照するのを忘れずに。
おまけ：ワンライナー(1行で処理するコード)
' 抽出件数のカウント Function Countフィルター抽出中の件数(指定シート As Worksheet) As Long フィルター抽出中の件数 = 指定シート.AutoFilter.Range.Columns(1).Resize(, 2).SpecialCells(xlCellTypeVisible).Count / 2 - 1 End Function
この記事を書くために、自分のユーティリティ関数を見たらこうなってました。
.Columns(1).Resize(, 2) で2列分の可視セルを数え、そのあと2で割ることで、単独セルになる罠を回避しています。
なるほど～とは思いますが、こういうパズル的な書き方はやってはいけません。
コードはとにかく読みやすさが大事です。
コードを5秒早く書いて、処理速度が1秒早くなっても、
後で見返してコードの意味を30秒考えたら、そのプログラムは遅いプログラムです。
このブログを書くために書き直す羽目になったので、とても遅いプログラムだったといえるでしょう。
