テーブルと通常フィルターが混在するシートにおけるAutoFilterの挙動

テーブル(ListObject)と通常のフィルター(AutoFilter)が混在したシートにおいて、
AutoFilterの各種プロパティがどのような挙動になるかを検証します。

サンプルシート

テーブル3つ、通常フィルター1つからなる以下のシートを検証に用います。

AutoFilterメソッド(フィルターの抽出)

Range("B3").AutoFilter 1, "みかん" ' A
Range("B12").AutoFilter 1, "みかん" ' B
Range("G3").AutoFilter 1, "みかん" ' C
Range("G12").AutoFilter 1, "みかん" ' F
Range("A1").AutoFilter 1, "みかん" ' F

' 以下は「AutoFilterメソッドが失敗しました」エラー
Range("D7:H14").AutoFilter 1, "みかん"
Range("B2:B3").AutoFilter 1, "みかん"

実行したRangeオブジェクトがテーブル内のセルであれば、
そのテーブルのフィルターが実行される。

そうでないセルはすべて通常のオートフィルターが実行される。
通常のフィルターと実行セルが重なっている必要はない。

テーブル内のセルとテーブル外のセルを両方とも含むセル範囲から実行すると、
「RangeクラスのAutoFilterメソッドが失敗しました」エラーとなる。

AutoFilterメソッド(フィルターの解除)

Range("B3").AutoFilter ' Aを解除
Range("B12").AutoFilter ' Bを解除
Range("G3").AutoFilter ' Cを解除
Range("G12").AutoFilter ' Fを解除
Range("A1").AutoFilter ' Fを解除

' 以下は「AutoFilterメソッドが失敗しました」エラー
Range("D7:H14").AutoFilter
Range("B2:B3").AutoFilter

抽出を実行するときとほぼ同じ仕様。

テーブルフィルターを解除した場合は、
フィルターが設置されていないテーブルになる。

※ フィルターボタンの表示チェックを外した状態ではなく、
フィルター自体がなくなって表示チェックはグレーアウトする。
（おそらく手作業でこの状態には設定できない）

テーブル内のセルとテーブル外のセルを両方とも含むセル範囲から実行すると、
「RangeクラスのAutoFilterメソッドが失敗しました」エラーとなる。

AutoFilterオブジェクト

?Worksheets("○○").ListObjects("テーブルA").AutoFilter.Range.Address ' (A) $B$3:$E$9
?Worksheets("○○").ListObjects("テーブルB").AutoFilter.Range.Address ' (B) $B$12:$E$18
?Worksheets("○○").ListObjects("テーブルC").AutoFilter.Range.Address ' (C) $G$3:$J$9

Range("B3").Select
?Worksheets("○○").AutoFilter.Range.Address ' (A) $B$3:$E$9
Range("G12").Select
?Worksheets("○○").AutoFilter.Range.Address ' (F) $G$12:$J$18
Range("A1").Select
?Worksheets("○○").AutoFilter.Range.Address ' (F) $G$12:$J$18
Range("D7:H14").Select
?Worksheets("○○").AutoFilter.Range.Address ' (A) $B$3:$E$9
Range("H14").Activate ' 選択セルをそのままにActiveCellだけを変更
?Worksheets("○○").AutoFilter.Range.Address ' (F) $G$12:$J$18
Range("B2:B3").Select
?Worksheets("○○").AutoFilter.Range.Address ' (F) $G$12:$J$18

テーブルのAutoFilterオブジェクトを取得したい場合は、
対象ListObjectのAutoFilterプロパティを取得すればよい。

問題は通常のオートフィルターのAutoFilterオブジェクトを取得する方法で、
WorksheetオブジェクトのAutoFilterプロパティは選択セルの影響を受ける。

選択範囲ではなくActiveCellによって取得するAutoFilterが決定される。
複数のテーブルをまたがるエリアが選択されていてもエラーにならない。

よって、通常のオートフィルターを確実に取得したい場合は、
テーブル内ではないセルを選択してから取得することになる。

最も確実なのはシートの使用範囲外を選択してから取得する方法で、
以下のコードは確実に通常のオートフィルターを取得する。

' UsedRangeより右側のセル = 確実にテーブルではないセルを選択
Worksheets("○○").Cells(1, Worksheets("○○").UsedRange.Columns.Count + 1).Select
?Worksheets("○○").AutoFilter.Range.Address ' (F) $G$12:$J$18

AutoFilterMode(フィルターの設置判定)

?Worksheets("○○").AutoFilterMode ' どのセルを選択していても(F)の状況を返す

こちらは選択セルの影響を受けないようで、どのセルを選択していても、
通常のフィルターがどこかに設置されているかどうかを判定する。

前述のとおりテーブルのフィルターもAutoFilterメソッドで解除が可能。

ただし、ListObjectオブジェクトはAutoFilterModeプロパティを持っていないため、
テーブルのフィルターが設置されているかどうかは以下のコードで判定する。

If Worksheets("○○").ListObjects("テーブルA").AutoFilter Is Nothing Then

AutoFilterMode=False(フィルターの解除)

Range("G12").Select
Worksheets("○○").AutoFilterMode = False ' Fが解除
Worksheets("○○").AutoFilterMode = False ' 何も起きず

' Fが解除された状態
Range("B3").Select
Worksheets("○○").AutoFilterMode = False ' 何も起きず

' Fにフィルターがある状態
Range("B3").Select
Worksheets("○○").AutoFilterMode = False ' ！Aが解除
Worksheets("○○").AutoFilterMode = False ' ！Aが再設置

一番謎な挙動、というかこれはVBAのバグと思われるので注意。

AutoFilterModeの仕様を考えると通常のフィルターにしか効かないはずだが、
なぜかテーブルフィルターにも影響してしまう。

通常のフィルターがついているとき(元がTrueのとき)だけ動き、
動くときは=Falseなのに解除/再設置がトグルで動くところを見ると、
実行条件は通常のフィルターなのに実行先がテーブルになる不具合のよう。

FilterMode(いずれかの列で抽出がかかっているか判定)

まず初めに、AutoFilterオブジェクトから呼び出した場合は、
そのフィルターの状態を取得できる。

?Worksheets("○○").AutoFilter.FilterMode ' 選択セルによる（AutoFilterオブジェクトの項を参照）
?Worksheets("○○").ListObjects("テーブルA").AutoFilter.FilterMode ' Aの状況を取得
?Worksheets("○○").ListObjects("テーブルB").AutoFilter.FilterMode ' Bの状況を取得
?Worksheets("○○").ListObjects("テーブルC").AutoFilter.FilterMode ' Cの状況を取得

テーブルであればListObjectオブジェクトからAutoFilterを呼べば確実だが、
通常のフィルターの抽出状況を選択セルによらずに調べる方法はない。

続いてWorksheetオブジェクトから直接FilterModeを参照した場合だが、
こちらはAutoFilterと同じ判定で選択セルから対象のフィルターが選ばれる。

よって結論としては、以下の2つのコードは常に同じ値を返す。

?Worksheets("○○").FilterMode
?Worksheets("○○").AutoFilter.FilterMode

　
通常のフィルターの抽出状況を調べたい場合は、
AutoFilterオブジェクトの時と同様、以下のコードを実行する。

' UsedRangeより右側のセル = 確実にテーブルではないセルを選択
Worksheets("○○").Cells(1, Worksheets("○○").Columns.Count + 1).Select
?Worksheets("○○").AutoFilter.FilterMode

ShowAllData(フィルターのクリア)

こちらもAutoFilterオブジェクトから実行した場合は、
そのフィルターをクリア(絞り込みを解除)することができる。

Worksheets("○○").AutoFilter.ShowAllData ' 選択セルに応じたフィルターをクリア
Worksheets("○○").ListObjects("テーブルA").AutoFilter.ShowAllData ' Aをクリア
Worksheets("○○").ListObjects("テーブルB").AutoFilter.ShowAllData ' Bをクリア
Worksheets("○○").ListObjects("テーブルC").AutoFilter..ShowAllData ' Cをクリア

AutoFilterオブジェクトから実行した場合は、
絞り込みがない(クリア済)だとしてもエラーにはならない。

今までの処理と同様、通常のフィルターをクリアしたい場合は下記コードを実行する。

' UsedRangeより右側のセル = 確実にテーブルではないセルを選択
Worksheets("○○").Cells(1, Worksheets("○○").UsedRange.Columns.Count + 1).Select
Worksheets("○○").AutoFilter.ShowAllData

続いてWorksheetオブジェクトから実行した場合。

Worksheets("○○").ShowAllData

こちらも今までと同様、選択セルに応じたフィルターがクリアされる。

ただしAutoFilterオブジェクトから実行した場合とは異なり、
どの列も抽出されていない(クリア済)の場合はエラーとなる。

これはExcelのフィルタークリアボタンがグレーアウトする挙動と一致する。

以上です。

なかなか複雑な仕様の上、Excelのバグまであって大変ですね。

ということで結論は「なるべく1シートにつき1フィルター」が原則と思いますが、
どうしても必要になったら本記事を参考にコーディングしてください。