えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

トップ > Excel > ExcelVBAでオートフィルタで絞り込んだデータの行数をカウントする

ExcelVBAでオートフィルタで絞り込んだデータの行数をカウントする

Excel VBA

ExcelVBAでオートフィルタで絞り込んだ結果、表示されているデータの行数をカウントする方法について説明する。

要望

最適解

アクティブなシートのフィルタ件数を知りたい場合は、下記のワンライナーを実行すれば良い。

ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

テーブルオブジェクトのフィルタ件数を知りたい場合は、次のような感じで実行すると良い。

ActiveSheet.ListObjects(1).AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1


ここから先は、何故このプログラムが最適解なのか、悪い例から順番に説明していく。



回答例

各行の表示状態を確認して非表示のものを数える

初学者さんが「ネットで調べて試行錯誤して完成した」という感じのを再現してみました。

とはいえ、シートオブジェクトの省略は有り得ないので、Dim ws As Worksheet: Set ws = ActiveSheetだけは無条件で付け足した。

Sub Poor_BeginnerCode()
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim last_row As Long
    last_row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim row_cnt As Long: row_cnt = 0
    Dim i As Long
    For i = 1 To last_row
        If ws.Cells(i, 1).EntireRow.Hidden = False Then
            row_cnt = row_cnt + 1
        End If
    Next
    
    Debug.Print row_cnt - 1
End Sub

簡単な説明

最初の行から最後の行までを順番に見ていって、非表示になっていない行をカウントする。というやりたい事をそのままプログラムに起こしたような流れです。


問題点は多数ありますが、まず行列指定の問題ですね。

last_row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 1 To last_row
  • ヘッダ行の位置が直接指定になっているので、ヘッダが動いたら直さないといけない
  • End(xlUp)なので、A列が空欄だと存在しないものとして省略されてしまう
  • 最終行を確認する列がA列固定になっているので、列が動いたら直さないといけない


もう一つの問題は、1行づつ非表示をチェックしているのでデータ量が増えるほど負担が重くなりやすいということです。

If ws.Cells(i, 1).EntireRow.Hidden = False Then

ただし、この処理の待ち時間が気になるかは状況によります。


オートフィルタの設定されたセル範囲のうち、可視セルだけ絞り込んでから行数を数える

実は私自ら書いて失敗したコードです。

Sub Poor_Rows_Count()
    Dim ws As Worksheet: Set ws = ActiveSheet

    Dim rng_filterd As Range
    Set rng_filterd = ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    
    Debug.Print rng_filterd.Rows.Count - 1
End Sub

このプログラムは完全に誤りです。期待通りには動きません。

解説

サンプルデータが以下の様な状態で実行した時、正しい答えは「3」になるはずですが、上記プログラムは「1」になります。

なぜなのか解説していきますね。


ws.AutoFilter.Range

これは、オートフィルタの設定されているセル範囲を取得するコードです。

ただし、これにはデータ部だけではなく、ヘッダ行も含まれます。

つまりB3:D8が対象です。


Set rng_filterd = .....SpecialCells(xlCellTypeVisible)

SpecialCellsが「可視セルだけ絞り込む」コードです。

Excelの「F5（ジャンプ）＞セル選択＞可視セル」の操作に相当します。

これによりフィルタで非表示になっている行が除外されます。

従ってrng_filterdには、期待通りB3:D4,B6:D7のセル参照が格納されます。


問題の原因はココにあります。

rng_filterd.Rows.Count

期待通りならココが「4」にならなければなりませんが、現実には「2」が返されます。


図で「2行」になっているところと言えば・・・

そう、SpeciallCellsによって行が分離されたブロックのAreas(1)の行数に相当します。

(サンプルの選定が不適切で、Areas(2)もAreas.Countも2ですが、ココで言う2行はAreas(1)です）

これがExcel VBA最大の鬼門と呼ばれるRangeオブジェクトの取り扱いの難しいところです。

Rangeオブジェクトから生えている（直接呼び出せる）プロパティは、状況によって親・間に補完されるオブジェクトが違います。

最初に紹介した最良の書き方Range.Countでは、Areasが離れていても全てのセルの合計数を返してくれます。

しかしRowsを含めた瞬間にAreas(1)が補完されたような動きをするため、Range.Rows.Countでは、Range.Areas(1).Rows.Countの結果・・・つまり「2」が返ります。


Areas毎の行数を合計する

というわけで、上記の問題をストレートに払拭するコードを書くとこうなります。

Sub Good_Areas_Entirerow_Count()
    Dim ws As Worksheet: Set ws = ActiveSheet

    Dim rng_filterd As Range
    Set rng_filterd = ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    
    Dim row_cnt As Long: row_cnt = 0
    
    Dim rng As Range
    For Each rng In rng_filterd.Areas
        row_cnt = row_cnt + rng.EntireRow.Count
    Next
    
    Debug.Print row_cnt - 1
End Sub

副作用は特にありません。

あえて上げるならば、次の点くらいです。

  • コードが冗長
  • 分離するデータ（Areas）が増えるほど速度が遅くなりやすい
  • SpecialCellsの副作用　※後述


SUBTOTAL関数の個数カウント機能を使う方法

ネットで検索すると、SUBTOTAL関数を使った書き方が大人気なようです。

Sub Good_Column1_Subtotal()
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim row_cnt As Long: row_cnt = 0
    row_cnt = WorksheetFunction.Subtotal(3, ws.AutoFilter.Range.Columns(1))
    
    Debug.Print row_cnt - 1
End Sub

問題点

SUBTOTAL関数ならではの不便なところが存在します。

注意が必要なのは次の点です。

  • 検証対象が先頭列に固定されている＝修正コストが発生
  • 空白セルが無視される＝正確な件数が返るとは限らない
  • Range型で返らない＝他の目的に応用が効かない

これらは大半の場合において問題とはならないため、期待通りの動きをしてくれることでしょう。


1列に絞って可視セルの個数を数える

最初に紹介した私の考える最適解です。

Sub Good_Column1_RangeCount()
    Dim ws As Worksheet: Set ws = ActiveSheet

    Dim rng_filterd As Range
    Set rng_filterd = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
    
    Debug.Print rng_filterd.Count - 1
End Sub

特徴

この書き方の他との違いは、次の2点です。

  • 早々に1列に絞っていること（ただし何列目を選んでも良い）
  • Rowsを使わずにCountを使用していること

Rowsは、既に説明したように、Areas(1).Rowsのような動きをするため、Countが期待通りに動きませんでした。

今回はRowsを使っていないため、一発でRangeのセルの全ての個数が取得できます。

代わりに問題となるのが、可視行数×列数となってしまうことです。

Range.Columns(1)

で、先に列数を1列に絞っておくことで、可視行数×1・・・つまり行数となるようにしています。


この方法なら速度は遅くなりにくいし、データが動いてもプログラム修正が必要な場所はない。

唯一懸念されるのは、SpecialCells特有の副作用である。※後述


注意事項

Worksheeet.AutoFilterプロパティ使用の注意事項

Activesheet.AutoFilterには重大な罠があることに注意したほうが良い。

このプロパティは、ActiveCellがテーブル（ListObject）の中にある時、ListObject.AutoFilterを返すという性質がある。

調べた限りでは、どのような状況下でもシートのオートフィルタを取得できるようなVBAは存在しなかった。※ActiveCell・ActiveSheetを変更しない限り

めったに問題となることはないと思うが注意して欲しい。

https://twitter.com/KotorinChunChun/status/1249930276626501635?s=20

img


SpecialCellsの副作用

先日の記事で書いた通り、SpecialCellsにはWorksheet_SelectionChangeイベントを発生させてしまうという性質があるらしい。

フィルタ結果の行数を計測する事は基本的に1回限りなので、問題となる確率は極めて低いが、もしイベントが起こるのが気に入らない場合は、今回の式の前後でApplication.EnableEvents = False / True を実行したほうが良さそうだ。


参考資料

きっかけはこのツイート

https://twitter.com/elve_hatena/status/1268513517692309507?s=20


まとめ

というわけで、いつもの「Rangeオブジェクトは難しい」話でした。

「Range.RowsにはRange.Areas(1).Rowsが隠れている」とだけ覚えて帰って頂ければ幸いです。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)