Excel のテーブルオートフィルターをVBAで制御

Excelのテーブルに自動的に設定されるオートフィルターをVBAから制御してみようと思いました。

ワークシートのオートフィルター

WikipediaのExcelを読むと、オートフィルターはExcel 4.0（古ッ!）で実装されたようです。そして、検索した感じではExcel 2007からテーブル機能が追加されたようです。

元々オートフィルターはワークシートに対して一つしか設定できなかったようです。

データを選んで

フィルターボタンを押すと

オートフィルターのボタンが表示されます

他のデータに設定するには、最初のフィルターは解除しなくてはいけません。

ワークシートのオートフィルターに対するVBAからのアクセス

ワークシートにはAutoFilterプロパティが含まれています。次のコードのEnd Subにブレークポイントを設定し、実行します。

Sub uMain()

    Dim uWS As Worksheet

    

    Set uWS = ActiveSheet

    uWS.AutoFilter.Range.Select

End Sub

uWS（ワークシートオブジェクト）のすぐ下にAutoFilterプロパティがあるのがわかります。

各列のフィルターの条件は、AutoFilterオブジェクトのFiltersオブジェクト（コレクション）の下にitemプロパティの形で存在します。

上のマクロでは、AutoFilterのRangeプロパティを使ってSelectしています。実行すると、次のように、オートフィルターが設定されているセルが選択状態になります。

問題なのは、AutoFilterオブジェクトは一つしか存在しないことです。当時はワークシートに一つしか存在しなかったので仕方がないことだと思いますが。

次のようにテーブルを追加すると、オートフィルターが二つ存在することになります。

上のマクロを実行してみたところ、アクティブセルがテーブル上に存在するとテーブルが選択され、それ以外のところではワークシートのオートフィルターが設定されたデータが選択されました。これが仕様かどうかは不明です。

テーブルを選択して実行

テーブル以外のセルを選択して実行

あまりないとは思いますが、ワークシートのオートフィルターとテーブルを併用する場合には思わぬ動作となる可能性があるので注意が必要です。

ワークシートのオートフィルターを操作するにはテーブル以外のセルを選択してから実行する必要がありそうです。

テーブルのオートフィルター

テーブルを作成すると自動的にオートフィルターが作られます。次の例では二つ作ってみました。

それぞれにオートフィルターが存在し、機能します。

テーブルのオートフィルターに対するVBAからのアクセス

テーブルに設定されたオートフィルターは、ListObjectのAutoFilterプロパティとしてアクセスできます。

次のマクロのEnd Subにブレークポイントを設定して実行します。

Sub uMain()

    Dim uWS As Worksheet

    Dim uLists As ListObjects

    

    Set uWS = ActiveSheet

    Set uLists = uWS.ListObjects

    

    uLists(1).AutoFilter.Range.Select

End Sub

実行すると、次のようにローカルウィンドウに表示されます。

ListObjects（テーブルコレクション）の一つ目のitem（テーブル）にAutoFilterオブジェクトがあることがわかります。

実行すると、次のように一つ目のテーブルが選択されます。

二つ目のテーブル（item 2）を指定すれば二つ目のテーブルが選択されます。

サンプル

テーブルのフィルターが使用されているかどうかを調べるマクロです。

作っている最中に一度AutoFilterがNothing状態だったことがありました。注意した方が良いでしょう。

'テーブルフィルターの使用状況（有効無効）を調べる

Sub uAutoFilter()

    Dim uLists As ListObjects

    Dim uList As ListObject

    Dim uFilter As Filter

    Dim i As Long

    

    Set uLists = ActiveSheet.ListObjects

    

    For Each uList In uLists

        With uList

            i = 0

            'AutoFilterがNothingの場合がある

            For Each uFilter In .AutoFilter.Filters

                i = i + 1

                Debug.Print .Name & " 列" & i & " " & uFilter.On

            Next

        End With

    Next

End Sub

実行結果（テーブルを一つ作って削除したためテーブル2から始まっています）

テーブル2 列1 False

テーブル2 列2 False

テーブル3 列1 False

テーブル3 列2 False

二つ目のテーブルの2列目にフィルターを設定して再度実行

テーブル2 列1 False
テーブル2 列2 False
テーブル3 列1 False
テーブル3 列2 True　（Trueになった）

テーブルのフィルターをOnOffするサンプルです。

AutoFilterプロパティがNothingの場合に対応してあります。

'フィルターをOnOffする

Public Sub gToggleFilter()

    Dim uList As ListObject

    Dim uIndex As Long

    

    Set uList = ActiveSheet.ListObjects("テーブル3")

    With uList

        uIndex = .ListColumns("金額").Index

        

        If .AutoFilter Is Nothing Then

            .Range.AutoFilter Field:=uIndex, Criteria1:="="

        Else

            If .AutoFilter.Filters(uIndex).On Then

                .Range.AutoFilter Field:=uIndex

            Else

                .Range.AutoFilter Field:=uIndex, Criteria1:="500"

            End If

        End If

    End With

End Sub

実行すると、フィルターが無効になるか、金額が500のものだけ選択されるかになります。

AutoFilterメソッド

ListOjbectのRangeオブジェクトを指定することによりテーブル全体が選択され、それに対して、uIndex（列番号）を指定することにより、列を指定することができます。

ListObject.Range.AutoFilter Field:=数字

上の命令は、テーブルのRangeプロパティ（テーブル全体のセル範囲）に対してAutoFilterメソッドを実行し、Fieldで列を指定しています。その列に対してAutoFilterが働くことになります。

ここで指定する列は、ワークシートに対する列ではなく、テーブルの列に対する相対的なものです。

例えばテーブルが3列であれば、テーブルがどこにあっても１から３の数字を指定することになります。上の例のように .ListColumnsを使ってインデックスを取得するのが良いでしょう。

Filterプロパティ

FiltersのFilterオブジェクトで条件等も取得できます。

ただし、設定を行うにはRangeオブジェクトに含まれるAutoFilterメソッドを使う事になります。プロパティと同じ名前で紛らわしいので注意が必要です。

まとめ

以下の点に注意をして扱うとよいでしょう。

1. オートフィルターはワークシートに付属するものとテーブルに付属するものの二つがある
2. ワークシートのAutoFilterプロパティで操作される対象はアクティブセルがどこかによって異なる
3. テーブルのオートフィルターはそれぞれのテーブルに存在する