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

Filterプロパティ

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

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


まとめ

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

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



コメント

過去の投稿を見る

もっと見る

アクセスランキング(過去30日間)

セキュリティ対策ソフトのノートンが詐欺ソフトまがいになってしまってショック

突然滅茶苦茶遅くなったPCがWindows Updateのキャッシュクリアで復活

Teamsで日本語入力すると左上に変換ウィンドウが出る

次期主力Wi-Fiルーター NEC Aterm WX5400HP

Amazon Prime Videoで4K UHD映画を検索する方法

コールマン タフスクリーンタープ/400 を購入しました

Excel 2019 クエリが原因で日本語入力の一文字目が勝手に確定する

iPhone 12 Proと iPhone 8 Plusのサイズを比較

Microsoft Flight Simulator (2020)のPS4コントローラー設定

テントやタープなど防水製品には寿命がある