オートフィルタの状況を判定する


大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。

  1. 書き方の基本
  2. 文字列で絞り込む
  3. 数値で絞り込む
  4. 日付で絞り込む
  5. 色で絞り込む
  6. 作業列で絞り込む
  7. 絞り込んだ結果をコピーする
  8. 絞り込んだ結果を集計する
  9. 絞り込んだ結果の行を操作する
  10. オートフィルタの状況を判定する (←このページ)

オートフィルタが設定されているかどうか

表のタイトルに、オートフィルタ矢印ボタン(▼ボタン)が表示されているかどうかを判定するには、ワークシートのAutoFilterModeプロパティを調べます。オートフィルタが設定されていれば(▼ボタンが表示されていれば)、Trueを返します。設定されていないときはFalseです。なお、オートフィルタは、1つのワークシートに1つしか設定できません。

Sub Macro1()
    If ActiveSheet.AutoFilterMode = True Then
        MsgBox "設定されています"
    Else
        MsgBox "設定されていません"
    End If
End Sub

絞り込まれているかどうか

オートフィルタが設定されているとき、実際に何らかの条件を指定して絞り込まれているかどうかを判定するには、AutoFilterオブジェクトのFilterModeプロパティを調べます。ここ、間違えやすいですから気をつけてください。先の「オートフィルタが設定されているかどうか」を調べるAutoFilterModeプロパティは、ワークシート(Worksheetオブジェクト)のプロパティです。対して「絞り込まれているかどうか」を調べるFilterModeプロパティは、AutoFilterオブジェクトのプロパティです。AutoFilterオブジェクトは「ワークシート.AutoFilter」で表されます。繰り返しますが、1つのワークシート上には1つのオートフィルタしか設定できません。

Sub Macro2()
    If ActiveSheet.AutoFilterMode = True Then
        If ActiveSheet.AutoFilter.FilterMode = True Then
            MsgBox "絞り込まれています"
        Else
            MsgBox "絞り込まれていません"
        End If
    End If
End Sub

「絞り込まれているかどうか」を調べるときは、上記のように、その前に「オートフィルタが設定されているかどうか」を判定してください。ワークシート上にオートフィルタが設定されていない状態(AutoFilterMode = False)でAutoFilterオブジェクトを操作しようとするとエラーになります。

どの列が絞り込まれているか

オートフィルタ全体に対して、どの列に条件が指定されているか(絞り込まれているか)を一発で調べる方法はありません。調べるには、オートフィルタ内の各列をひとつずつ確認します。オートフィルタの各列はFilterオブジェクトで表されます。すべての列はFilersコレクションです。何らかの条件が指定されているFilterオブジェクトは、OnプロパティがTrueを返します。

下図は「1列目が"田中"である」という条件と「3列目が50より小さい」という条件で絞り込んでいます。

Sub Macro3()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & i & "列目" & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

上記のように、絞り込まれている列の位置を調べるのは簡単ですが、その列のタイトルを取得するのは面倒くさいです。たいていの場合は、何列目という位置が分かれば十分ですが、もしタイトルを調べたいなら、次のようにします。

Sub Macro4()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & .AutoFilter.Range(i) & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

何という条件で絞り込まれているか

設定されている条件を調べるのは大変です。なぜなら、オートフィルタにはさまざまな絞り込み方法が用意されているからです。単純に「"田中"と等しい」や「50より小さい」だけでなく、複数の条件をAndやOrで指定できたり、トップテンや色による絞り込みなども可能です。それらの、すべての判定方法を解説すると煩雑になるので、ここでは一般的によく設定される条件をいくつかご紹介します。

1つの条件が設定されているとき

「1列目が"田中"と等しい」とか「3列目が50より小さい」など、1列に1つの条件が設定されているケースです。

そもそもオートフィルタを設定するときは、

のように指定します。引数Fieldは、条件を設定する列の位置です。そして、1つめの条件を引数Criteria1に指定します。実はFilterオブジェクトには、この引数Criteria1と同じ名称のCriteria1プロパティがあります。このプロパティを調べることで、引数Criteria1に指定した条件が分かります。下図は「1列目が"田中"と等しい」で絞り込んでいます。

Sub Macro4()
    MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1
End Sub

2つの条件が設定されているとき

下図は「3列目が"40より大きい"かつ"80より小さい"」で絞り込んでいます。

1つめの条件は、先のようにCriteria1プロパティで分かります。同様に、2つめの条件はCriteria2プロパティです。

Sub Macro5()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Criteria1 & vbCrLf & .Criteria2
    End With
End Sub

さて問題は「かつ(AND)」です。ここまでの流れを理解された方なら、おそらくFilterオブジェクトには、引数Operatorと同じ名前のOperatorプロパティがあるのではないかと想像できるでしょう。その通りです。引数Operatorに指定した「かつ(AND)」や「または(OR)」などは、Operatorプロパティを調べれば分かります。ちょっと、やってみましょう。

Sub Macro6()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Operator
    End With
End Sub

この「1」とは何でしょう。オートフィルタの設定をVBAでやるとき、たとえば今回のような「3列目が"40より大きい"かつ"80より小さい"」でしたら、次のように書きます。

Range("A1").AutoFilter 3, ">40", xlAnd, "<80"

このとき、引数Operatorに指定した定数xlAndの実体が「1」です。引数Operatorに指定できる定数は、次のとおりです。

定数 実体 意味
xlAnd 1 AND
xlOr 2 OR
xlTop10Items 3 トップテン
xlBottom10Items 4 トップテン
xlTop10Percent 5 トップテン
xlBottom10Percent 6 トップテン
xlFilterValues 7
xlFilterCellColor 8 セルの色
xlFilterFontColor 9 フォントの色
xlFilterIcon 10 アイコン
xlFilterDynamic 11 動的フィルタ
xlFilterNoFill 12 セルの色(なし)
xlFilterAutomaticFontColor 13 フォントの色(自動)
xlFilterNoIcon 14 アイコン(なし)

FilterオブジェクトのOperatorプロパティが返すのは、これら"実体"の数値です。つまり、Operatorプロパティで取得できる数値から、上記のいずれかを導かなければなりません。本項の冒頭で「設定されている条件を調べるのは大変です」と書いたのは、こういう理由です。ちなみに「1列目が"田中"と等しい」のように、1つの列に1つの条件しか指定していないとき、つまり引数Operatorを指定していないときは、Operatorプロパティが0を返します。

以上をふまえた上で、少しだけ実用的なコードをご紹介します。下図は「1列目が"田中"または"広瀬"である」と「3列目が"40より大きい"かつ"80より小さい"」で絞り込んでいます。

Sub Macro7()
    Range("A1").AutoFilter 1, "田中", xlOr, "広瀬"
    Range("A1").AutoFilter 3, ">40", xlAnd, "<80"
End Sub

これらの条件を判定するには、次のように面倒くさくなりますね。

Sub Macro9()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            With .AutoFilter
                For i = 1 To .Filters.Count
                    If .Filters(i).On = True Then
                        msg = msg & i & "列目:" & vbCrLf
                        Select Case .Filters(i).Operator
                        Case 0
                            msg = msg & .Filters(i).Criteria1 & vbCrLf
                        Case 1
                            msg = msg & .Filters(i).Criteria1 & " AND " & .Filters(i).Criteria2 & vbCrLf
                        Case 2
                            msg = msg & .Filters(i).Criteria1 & " OR " & .Filters(i).Criteria2 & vbCrLf
                        End Select
                    End If
                Next i
            End With
        End If
    End With
    MsgBox msg
End Sub

3つ以上の条件が設定されているとき

1つの列に、3つ以上の条件が設定されているケースです。下図は「1列目が"田中"または"広瀬"または"桜井"である」という条件で絞り込んでいます。

VBAでやるなら、次のような感じです。

Sub Macro10()
    Range("A1").AutoFilter 1, Array("田中", "広瀬", "桜井"), xlFilterValues
End Sub

このように、1つの列に3つ以上の条件を指定するときは、引数Criteria1に配列を指定します。すると、今まで条件を調べていたFilterオブジェクトのCriteria1プロパティも配列になります。

Sub Macro11()
    With ActiveSheet.AutoFilter.Filters(1)
        If IsArray(.Criteria1) = True Then
            MsgBox "配列です"
        Else
            MsgBox "配列ではありません"
        End If
    End With
End Sub

指定されている条件は、この配列(Criteria1プロパティ)の要素を調べます。

Sub Macro12()
    Dim C As Variant, msg As String
    With ActiveSheet.AutoFilter.Filters(1)
        If IsArray(.Criteria1) = True Then
            For Each C In .Criteria1
                msg = msg & C & vbCrLf
            Next C
        End If
    End With
    MsgBox msg
End Sub

色の条件が設定されているとき

下図のような表で

「1列目の塗りつぶし色が"赤"である」という条件で絞り込むには、次のようにします。

Sub Macro13()
    Range("A1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
End Sub

このように、オートフィルタの条件にセルの塗りつぶし色を指定したとき、FilterオブジェクトのCriteria1プロパティは、Interiorオブジェクトを返します。意味が分からない方は、とりあえず"そういうもんだ"と思ってください。

Sub Macro14()
    With ActiveSheet.AutoFilter.Filters(1)
        MsgBox TypeName(.Criteria1)
    End With
End Sub

なので、条件に指定した色は、InteriorオブジェクトのColorプロパティで分かります。

Sub Macro15()
    With ActiveSheet.AutoFilter.Filters(1)
        MsgBox .Criteria1.Color
    End With
End Sub

もっとも、今回は「混じりっけなし、純色の"赤"」を指定したので、赤を表すRGB値「255」が返りましたが、もっと変な色だったら変なRGB値が返ります。

「9359529」って言われてもねぇ~w せめてRがいくつ、Gがいくつ、Bがいくつという数値が知りたいところです。ちなみに、手動操作で調べるのでしたら簡単です。塗りつぶし色が設定されているセルを選択して、リボンの[塗りつぶしの色]ボタンから[その他の色]を選択します。

表示される[色の設定]ダイアログボックスにRGBの値が示されます。

まぁ、こんなことは、めったにやらないでしょうけど、このRGB値を計算によって求めるには次のようにします。詳しくは「色パレットに登録されている色を調べる」をご覧ください。

Sub Macro16()
    Dim R As Long, G As Long, B As Long
    With ActiveSheet.AutoFilter.Filters(1).Criteria1
        B = Int(.Color / 65536)
        G = Int((.Color - (B * 65536)) / 256)
        R = .Color - (G * 256) - (B * 65536)
    End With
    MsgBox "R:" & R & vbCrLf & _
           "G:" & G & vbCrLf & _
           "B:" & B
End Sub