1

このスレッドをきっかけに、Excelフィルタ系ライブラリを深堀りました。

https://winactor.com/questions/?q=フィルター後、計算しません。何が原因なのでしょうか。

「セル位置」設定の際に注意を要する点を整理しました。
フィルタ結果に応じた再計算を伴う、SUBTOTALみたいな関数を併用する際に効いてきます。
長めですが、結論だけでも十分です。
太字を中心に眺めていただくと、必要な場面で情報を探すきっかけになったり、ハマらずに済むはずです。
後半の▼以下が検証結果です。

 

検証で見出した結論

  • 原則
    • WinActorからExcelフィルタ系ライブラリを使う場合は、予め表を孤島にしておくべし
    • セル位置には表内左上のセル番地を指定すべし
  • 例外
    • 表を孤島にできない事情がある場合は、セル位置に行範囲を指定すべし
    • フィルタ結果に伴うSUBTOTAL等の再計算が必要な場合は、Excelフィルタ系ライブラリを直列で複数並べてダメ押しすべし

 

検証環境

  • WinActor:7.2
  • Excel:Microsoft 365(2019相当)

今回の検証は7.2だけです。
Excel起因っぽいので、結論はWinActorのバージョンを問わず有効なはずです。

 

特定済みの挙動と再現条件

  • ★は別回答で特定済みの症状
  • Zawawaさんが特定されたのは☆☆のケースで、表が孤島になっている限り有効
    (海なし県、埼玉みたいな表だと▲)

 

「セル位置」相当のRangeオブジェクトに関する主なリファレンスサイトの要約

  • 表内のどこのセルでも良い
  • 左上のセルが分かりやすい
  • 範囲でも良い
  • Excelはブランクセルで囲われた範囲を表とみなす
    ※ 行指定「Rows(3)」の例は見つかるものの、3:3の例は見つけられず、、
    ※ Rows(3)がおっけーなら、3:3も許されるはず

 

同じ操作のマクロ実行結果

  • 選択行をカラム行と位置付けてフィルタできた
  • 孤島である必要はなかった
  • 1度で正しく再計算された
  • 選択行以下で、ブランクセルで囲われた範囲に対して、フィルタがかかった
  • VBAコード最適化の過程で、再計算されない症状を再現できた

 

別回答と同じ表での検証結果

1行目:SUBTOTAL
2行目:カラム名
3行目以下:データ

  • 2:2 → ★
  • A2:C2 → ★
  • A2 → 1行目にフィルタがかかり、期待通りの挙動ならず▲
  • 2 → 指定されたセルが見つかりません
  • A2:C20 → 1度で正しく再計算された

マクロでも確認

Rows(2:2).Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$C$20").AutoFilter Field:=2, Criteria1:="40"

→ 1度で正しく再計算された

プルダウン表示のくだりを省略

ActiveSheet.Rows(2:2).AutoFilter Field:=2, Criteria1:="40"

→ ★

  • WinActorのExcelフィルタ系ライブラリで、セル位置に2:2やA2:C2をセットした場合にSUBTOTALの再計算が走らないのは、Excelと同じ挙動で、少なくともWinActorのバグという訳ではなかった
  • WinActorライブラリもExcel VBAも、少なくとも使いかたで回避可能

Excelの扱いかたの問題なのかバグなのか、時間が取れればリサーチしてみようと思います。
情報が見つからなかったら、Microsoftコミュニティにあたりに質問してみようかと。

 

▼ 2行目に空行を挿入した構成で再検証

1行目:SUBTOTAL
2行目:空行
3行目:カラム名
4行目以下:データ

  • 3:3 → ★
  • 4:4 → 4行目にフィルタがかかった
  • A3:C3 → ★
  • A4:C4 → 4行目にフィルタがかかった
  • A3 → 1度で正しく再計算された☆☆
  • A4、B4、C4、A20、B20、C20 → 1度で正しく再計算された☆
  • A3:C21 → 1度で正しく再計算された
  • A3:C20 → 1度で正しく再計算された、絞込みキーが「セル位置」の範囲外、21行目にあってもヒットした
  • B3:C21 → 1度で正しく再計算された、A列にフィルタのプルダウンはないものの結果に影響なし
  • B5:B10 → 5行目にフィルタがかかった
  • B5:C10 → 5行目にフィルタがかかった
    ※ いずれも表に存在しない列番号を指定したら、RangeクラスのAutoFilterメソッドに失敗

 

オートフィルタのリファレンス

Office TANAKA - Excel VBA Tips[オートフィルタ[書き方の基本]]
http://officetanaka.net/excel/vba/tips/tips155.htm

【VBA入門】オートフィルターを設定・解除する方法を徹底解説! | 侍エンジニアブログ
https://www.sejuku.net/blog/71689

【Excel VBA】オートフィルタの設定・絞り込み・解除の方法
https://works.forward-soft.co.jp/blog/detail/10294

【EXCEL VBA】フィルターを使ってデータを絞りこむ方法 | きままブログ
https://masagoroku.com/【excel-vba】フィルターを使ってデータを絞りこむ方法

Range.AutoFilter メソッド (Excel) | Microsoft Docs
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter

【エクセルマクロ】オートフィルタの範囲設定:4つの使用例を紹介するよ | kirinote.com
https://kirinote.com/excelvba-autofilter-set/

[Excel VBA]フィルターの適用範囲を調べるには? | 日経クロステック(xTECH)
https://xtech.nikkei.com/it/atcl/column/15/090100207/090100047/

セルのAutoFilterメソッド - Excel VBA
https://www.239-programing.com/excel-vba/basic/basic06H.html

anothersolution 質問の投稿
回答とコメントは、会員登録(無料)で閲覧できるようになります。