【VBA】14.1 オートフィルター（AutoFilter）を使ってみよう！

2024年10月6日

AutoFilterメソッドの概要

AutoFilterメソッドは、Excel上の「フィルター」と同等の機能を実現します。
（Excelの古いバージョンでは、オートフィルターと呼ばれていました。）
ワークシートからデータを抽出する際には、欠かせない機能です。

[overdoserの経験則] AutoFilterメソッドを実際の業務で使用するケース

フィルターはExcelマクロ以外でも、手動でワークシート上のデータの絞り込みを行う際に、皆様も利用されていると思いますので、イメージしやすいかと思いますが、Excelマクロでは手動では煩雑で実施するのが難しい、ループ処理などで力を発揮します。

社内システムの各機能毎にどの部署の使用頻度が高いか、という調査を行うために、社内システムにログに出力される機能IDと部署コードをExcelの作業用ワークシートに展開し、まず機能IDを全件ループするForループ文を作成し、その中に部署コードを全件ループするForループ文を作成し、その中で機能IDと部署コードを用いてAutoFilterメソッドでフィルターし、その件数をSubtotal関数でカウントし、その結果をExcel形式の出力ファイルに出力する。
社員一覧データから、来月1ヵ月に退職予定の社員を、AutoFilterメソッドで「来月1日 ≦ 退職予定日 ≦ 来月最終日」で絞り込み、該当社員の社員番号を取得する。社内の収集しているインターネットのアクセスログファイルをExcelマクロで読み込み、退職予定の各社員毎に、不正や操作が行われているかどうかを社員番号と各種キーワード等を組み合わせて、AutoFilterメソッドでフィルターし、その件数をSubtotal関数でカウントし、各社員毎の件数をExcel形式の出力ファイルに出力する。

AutoFilterメソッドの構文

AutoFilterメソッドの構文は以下の通りです。
（AutoFilterを設定するワークシート上の表領域の左上部のセルが「A1」の場合とする。）

Worksheet変数.Range(“A1″).AutoFilter Field:=”設定値”, Criteria1:=”設定値”, Operator:=”設定値”, Criteria2:=”設定値”, VisibleDropDown:=”設定値”

引数名	必須/任意	引数への設定値
Field	必須	抽出条件の対象となる列番号を、表領域の左端の列から数えた数値を設定します。
Criteria1	必須	1つ目の抽出条件を設定します。
Operator	任意	フィルターの種類をXlAutoFilterOperatorクラスの定数で指定します。利用頻度が多いのは、以下の3種類です。・xlAnd（AND条件）・xlOr（OR条件）・xlFilterValues
Criteria2	任意	2つ目の抽出条件を設定します。
VisibleDropDown	任意	True または省略時は、フィルターのドロップダウン矢印を表示し、Falseの場合は表示されません。

Criteria1、Criteria2には、以下のように抽出条件を設定します。

抽出条件	設定値
Aと等しい	“=A”
Aと等しくない	“<>A”
Aを含む	“=A”
Aを含まない	“<>A”
100より大きい	“>100”
100以上	“>=100”
100より小さい	“<100”
100以下	“<=100”
空白セル	“=”
空白以外のセル	“<>”

上記にあるように、設定値には「*」、「?」の2種類のワイルドカードを使用できます。
また、「Criteria1:=変数」と指定することにより、設定値には変数を指定することもできます。

AutoFilterメソッドの使用例

AutoFilterメソッドを実際に使用したコーディング例は、以下の通りです。

    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:="=B021", Operator:=xlOr, Criteria2:="=B031"

社員一覧シートのA1セルを含む表領域の1列目に、以下の設定でフィルターを設定する。
フィルターの設定を、6列目（F列）に対して実施する。
フィルターの条件は、『6列目の値が「B021」または「B031」のもの』を抽出する。

上記のコードを実際に動作するマクロで実行すると、以下のように出力されます。

[応用例] AutoFilterメソッドを使ったコーディング例

以下のコーディング例は、マクロで社員一覧ファイルをOpenし、上記のコードで社員一覧シートをAutoFilterメソッドで6列目をフィルターし、社員一覧ファイルを更新して保存する、というものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:="=B021", Operator:=xlOr, Criteria2:="=B031"

    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員一覧をフィルターしました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

[応用編] ソースコードの解説

開始行数	終了行数	処理概要
17	25	マクロシートに記載された入力ファイルをOpenし、入力ファイル用のワークブック変数、ワークシート変数を設定する。
27	27	社員一覧シートに対して、AutoFilterの設定を行っている。
29	29	入力ファイルをCloseしている。
31	37	後処理を行っている。

[emalabo式] フィルターした後の表に対して、別の操作を行う場合の常套手段

フィルターした後の表に対して、表示されている表に直接何らかの操作を行おうとした場合、フィルターによって隠れているデータも処理の対象となるため、思い通りの結果が出ない、ということがあると思います。
そのような場合に、SpecialCellsメソッドを使って、表領域の見えている部分のみ（xlCellTypeVisible）を対象にして別のワークシートにコピーした後、コピー後のワークシートで別の操作を行う、とすることより解決できます。

    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:="=B021", Operator:=xlOr, Criteria2:="=B031"

    ws_employeelist.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=ws_work.Range("A1")

社員一覧シートに対して、AutoFilterメソッドを設定します。
社員一覧シートのA1セルを起点とするCurrentRegionに対して、SpecialCellsメソッドで可視セルのみ（xlCellTypeVisible）をコピーし、workシートのA1セルにペーストします。
以降はworkシートに対して、何らかの操作を行うコードを記述します。

[emalabo式] フィルター処理を行う前後でフィルターを外す場合の常套手段

以下のような場合には、フィルターを外した方が良い場合があります。

フィルター処理を行う表領域に対して、既にフィルターが掛かっている場合、更にそこにAutoFilterメソッドでフィルター処理を実施すると、不正な結果となる場合。
マクロの処理で一時的にAutoFilterメソッドでフィルター処理を実施した後、元のフィルターが掛かっていない状態に戻す必要がある場合。

表領域にフィルターが掛かっている状態で、引数なしのAutoFilterメソッドを設定すると、フィルターを外すことができるため、以下の処理を行うことにより、安全かつ確実にフィルターを外すことができます。

AutoFilterModeプロパティで指定のワークシートにフィルターが掛かっているかをチェックする。
チェックの結果、フィルターが掛かっている場合は、引数なしのAutoFilterメソッドを実行することにより、フィルターが外れる。

以下のコードでは、社員一覧シート（ws_employeelist）のフィルター設定を確認し、フィルターが設定されている場合にフィルターを外します。

    If ws_employeelist.AutoFilterMode Then
        ws_employeelist.Range("A1").AutoFilter
    End If

様々なケースに応じたAutoFilterメソッドの記述方法

これ以降ページでは、以下のような様々なケースに応じたAutoFilterメソッドのコードの記述方法について解説します。

同一の表領域に対して、複数の列のフィルターを設定する場合
1つの列に対して、3つ以上の抽出条件を設定してフィルターする場合
フィルターの条件にセルの背景色を設定する場合
フィルターの条件にセルに設定された条件付き書式を設定する場合
フィルターの条件に日付を設定する場合

同一の表領域に対して、同時に複数の列のフィルターを設定する場合

同一の表領域に対して、複数の列のフィルターを設定するには、それぞれの列をフィルターするAutoFilterメソッドのコードを複数行設定します。
以下のコードは、社員一覧シートの5列目と6列目のそれぞれの列に対するフィルターの設定を列挙することにより、5列目と6列目の両方の条件に合致したもののみ、表示されます。

5列目は、部署コード＝「A01001」でフィルターする。
6列目は、役職コード＝「B021」または「B031」でフィルターする。

    ws_employeelist.Range("A1").AutoFilter field:=5, Criteria1:="=A01001"
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:="=B021", Operator:=xlOr, Criteria2:="=B031"

上記のコードを実際に動作するマクロで実行すると、以下のように出力されます。

[応用例] 同一の表領域に対して、同時に複数の列のフィルターを設定するコーディング例

以下のコーディング例は、マクロで社員一覧ファイルをOpenし、上記のコードで社員一覧シートをAutoFilterメソッドで5列目と6列目をフィルターし、社員一覧ファイルを更新して保存する、というものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    If ws_employeelist.AutoFilterMode Then
        ws_employeelist.Range("A1").AutoFilter
    End If

    ws_employeelist.Range("A1").AutoFilter field:=5, Criteria1:="=A01001"
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:="=B021", Operator:=xlOr, Criteria2:="=B031"

    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員一覧をフィルターしました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

1つの列に対して、3つ以上の抽出条件でフィルターする場合

1つの列に対して、3つ以上の抽出条件を設定してフィルターするには、Criteria1に抽出条件の値（値は3つ以上）をセットした配列を設定し、Operatorに「xlFilterValues」を設定します。
なお、上記の方法で抽出できるのは、以下の条件のみです。

配列の値と等しいものを抽出する。（等しいもの以外は抽出できない。）
全ての抽出条件のOR条件を抽出する。

以下のコードは、配列に値をセットし、それをAutoFilterのCriteria1にセットすることにより、3つ以上の抽出条件でのフィルターを実現しています。

str_position(2)という3つの要素を持った文字列配列を宣言する。
str_position()という3つの要素を持った文字列配列に、「B021」、「B031」「B999」をセットする。
AutoFilterのCriteria1に「str_position()」をセットし、Operatorに「xlFilterValues」をセットし、6列目でフィルターする。

    Dim str_position(2) As String

    str_position(0) = "B021"
    str_position(1) = "B031"
    str_position(2) = "B999"
    
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=str_position(), Operator:=xlFilterValues

上記のコードを実際に動作するマクロで実行すると、以下のように出力されます。

[応用例] 1つの列に対して、3つ以上の抽出条件でフィルターする場合のコーディング例

以下のコーディング例は、マクロで社員一覧ファイルをOpenし、配列に3つの抽出条件の値をセットし、社員一覧シートをAutoFilterメソッドで6列目を配列にセットされた値でフィルターし、社員一覧ファイルを更新して保存する、というものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim str_position(2) As String       '3つの抽出条件を設定する配列
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    If ws_employeelist.AutoFilterMode Then
        ws_employeelist.Range("A1").AutoFilter
    End If

    '配列に、3つの抽出条件を設定する。
    str_position(0) = "B021"
    str_position(1) = "B031"
    str_position(2) = "B999"
    
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=str_position(), Operator:=xlFilterValues

    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員一覧をフィルターしました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

フィルターの条件にセルの背景色を設定する場合

フィルターの条件にセルの背景色を設定する場合、AutoFilterのそれぞれの引数に、以下のような値をセットします。

Criteria1:=RGB(255, 255, 255)
Operator:=xlFilterCellColor

Criteria1には、RGB関数でフィルターで抽出したい色を設定します。
主な色は、以下の通りです。

黒：RGB(0, 0, 0)
白：RGB(255, 255, 255)
赤：RGB(255, 0, 0)
黄：RGB(255, 255, 0)

Operatorには、XlAutoFilterOperatorクラスの定数の中から、セルの色でフィルターを実行する際の定数である「xlFilterCellColor」をセットします。

    ws_employeelist.Range("A1").AutoFilter field:=5, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

入力ファイルの社員一覧シートのE列において、以下のようにセルの背景色が黄色のものあると仮定します。

上記の社員一覧シートに対して、上記のコードを実行すると、以下の結果となります。

[応用例] フィルターの条件にセルの背景色を設定する場合のコーディング例

以下のコーディング例は、マクロで社員一覧ファイルをOpenし、社員一覧シートの5列目の各セルに対して、AutoFilterメソッドで背景色が黄色のものでフィルターし、社員一覧ファイルを更新して保存する、というものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    If ws_employeelist.AutoFilterMode Then
        ws_employeelist.Range("A1").AutoFilter
    End If
   
    ws_employeelist.Range("A1").AutoFilter field:=5, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員一覧をフィルターしました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

[emalabo式] フィルターの条件にセルに設定された条件付き書式を設定する場合

社員一覧シートの6列目に対して、以下の条件付き書式が設定されているとします。

この場合、社員一覧シートは以下のように出力されます。

この社員一覧シートを見る限りでは、6列目の一部のセルに黄色と赤の背景色があるだけのように思われるため、「7-5 フィルターの条件にセルの背景色を設定する場合」でご説明した方法でフィルターをしても、正常にフィルターされません。

条件付き書式で設定された色でフィルターする場合、AutoFilterのそれぞれの引数に、以下のような値をセットします。

Criteria1:=ws_inputfile.Columns(6).FormatConditions(1).Interior.Color
Operator:=xlFilterCellColor

上記のCriteria1についてご説明します。

ws_inputfile.Columns(6)：まず、条件付き書式が設定されている列を特定できる情報をセットします。
FormatConditions(1)：上記で指定した列に設定されている条件付き書式で、何番目の条件の色でフィルターするかを「()カッコ」の中に数字をセットします。例えば、上記の「条件付き書式ルールの管理」ウィンドウに2つの条件がありますが、先に作成したものが表の位置は上になるため、FormatConditions(1)は一番上の赤で背景色をフィルターします。

    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=ws_employeelist.Columns(6).FormatConditions(1).Interior.Color, Operator:=xlFilterCellColor

上記の社員一覧シートに対して、上記のコードを実行すると、以下の結果となります。

[応用例] フィルターの条件にセルに設定された条件付き書式を設定する場合のコーディング例

以下のコーディング例は、マクロで社員一覧ファイルをOpenし、社員一覧シートの6列目に設定された2つの条件付き書式のうち、1番目の条件付き書式に設定された赤でセルの背景色をフィルターし、社員一覧ファイルを更新して保存する、というものです。

Option Explicit

Sub extract_employeelist()

    Dim ws_macro As Worksheet           '社員データ抽出マクロファイル内の社員データ抽出マクロシートのワークシート変数
    Dim ws_work As Worksheet            '社員データ抽出マクロファイル内のworkシートのワークシート変数
    Dim wb_inputfile As Workbook        '入力ファイルのワークブック変数
    Dim ws_employeelist As Worksheet    '入力ファイルの社員一覧シートのワークシート変数
    Dim path_inputfile As String        '入力ファイルのフルパスを設定する変数
    Dim num_row As Long                 'Forループ分のカウンター
    
    Application.ScreenUpdating = False

    Set ws_macro = ThisWorkbook.Worksheets("社員データ抽出マクロ")
    Set ws_work = ThisWorkbook.Worksheets("work")
    
    If Right(ws_macro.Range("D3"), 1) <> "\" Then
        path_inputfile = ws_macro.Range("D3") & "\" & ws_macro.Range("C3")
    Else
        path_inputfile = ws_macro.Range("D3") & ws_macro.Range("C3")
    End If
    
    Workbooks.Open Filename:=path_inputfile
    Set wb_inputfile = ActiveWorkbook
    Set ws_employeelist = ActiveWorkbook.Worksheets("社員一覧")
    
    If ws_employeelist.AutoFilterMode Then
        ws_employeelist.Range("A1").AutoFilter
    End If
   
    ws_employeelist.Range("A1").AutoFilter field:=6, Criteria1:=ws_employeelist.Columns(6).FormatConditions(1).Interior.Color, Operator:=xlFilterCellColor

    wb_inputfile.Close SaveChanges:=True
    
    MsgBox "社員一覧をフィルターしました。"
    
    ws_macro.Activate
    
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub

[次の記事]

emalabo.com

【VBA】14.2 並べ替え機能（Sortオブジェクト）を使ってみよう！ Excel VBAのSortオブジェクトを使ったデータの並べ替えの方法を解説します。ソートキーの設定や具体的なコード例を通じて、複数条件での並べ替えの実装方法を詳しく説明し…

この記事を書いた人

overdoser

某IT企業に勤務。

2年間の休職を経て、一から独学でExcel VBAを習得し、半年でExcel VBA エキスパート資格のスタンダードに一発合格。

社内の非効率的な作業を効率化するExcelマクロの開発を、全行程まるっと一人で請け負い、これまでに約100個のExcelマクロを開発し、現在も増産中。