【Excel VBA】オートフィルタを操作する

このページで書くこと

Excel VBAでオートフィルタを使用する際の書き方や注意点を、実務に照らして書きます。
数値や文字列でのフィルタリングを一通りできるようにすることが目標です。

経緯

Excel VBAのオートフィルタには多くの機能がありますが、実際に使用するのはごく一部の機能だけです。
数値と文字列でフィルタリングできれば、実務ではほぼ困りません。

しかし、よく使う機能のみを簡単にまとめたサイトがなかなか見つからないので、このページにまとめました。

使用するデータ

Excelで以下のようなサンプルデータを作成しました。
特筆すべき点としては「山田勇気」と「山田勉」の、二人の山田さんがいます。

f:id:arekoretips:20210606052412j:image:w400 — サンプルデータ

使用するコード

ベーシックなフィルタリング

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田勇気」でフィルタリングします。
※「山田勇気」の行のみが表示されます。

f:id:arekoretips:20210606052741j:image:w400 — 1行目のB列を「山田勇気」でフィルタリング

Sub AUTOFIL()
'
    Dim keyWord As String
    '
    keyWord = "山田勇気"
    '
    '「成績表」シートの1行目のB列を「山田勇気」でフィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter Field:=2, Criteria1:=keyWord
'
End Sub

省略した書き方

上のコードの省略版です。
「Field:=」や「Criteria1:=」は書かなくても動作上問題ありません。
※私はあとでわかりやすいように書いていますので、以降はすべて省略せずに書いていきます。

Sub AUTOFIL_2()
'
    Dim keyWord As String
    '
    keyWord = "山田勇気"
    '
    '「成績表」シートの1行目のB列を「山田勇気」でフィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter 2, keyWord
'
End Sub

ワイルドカードを用いたフィルタリング

「*」を使えば「〇〇を含む」や「〇〇から始まる」といった検索方法も可能です。

*〇〇	〇〇で終わる
〇〇*	〇〇から始まる
〇〇	○○を含む

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田を含む」でフィルタリングします。
※「山田勇気」と「山田勉」の行が表示されます。

f:id:arekoretips:20210606053118j:image:w400 — 1行目のB列を「山田を含む」でフィルタリング

Sub AUTOFIL_WILD()
'
    Dim keyWord As String
    '
    keyWord = "山田"
    '
    '「成績表」シートの1行目のB列を「山田を含む」で フィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter Field:=2, Criteria1:="*" & keyWord & "*"
'
End Sub

複数条件でフィルタリング

「Criteria」は「Criteria1」「Criteria2」と繋げることでフィルタリングの条件を追加することができます。
その際、「Operator」に指定する定数を変更することで、And検索やOr検索が可能です。

xlAnd	条件A且つ条件B
xlOr	条件Aまたは条件B

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田を含む」か「佐藤淳」でフィルタリングします。
※「山田勇気」「山田勉」「佐藤淳」の行が表示されます。

f:id:arekoretips:20210606054036j:image:w400 — 1行目のB列を「山田を含む」か「佐藤淳」でフィルタリング

Sub AUTOFIL_MULT()
'
    Dim keyWord As String
    Dim keyWord2 As String
    '
    keyWord = "山田"
    keyWord2 = "佐藤淳"
    '
    '「成績表」シートの1行目のB列を「山田を含む」か「佐藤淳」でフィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter Field:=2, _
        Criteria1:="*" & keyWord & "*", Operator:=xlOr, _
        Criteria2:=keyWord2
'
End Sub

配列を使ったフィルタリング

複数条件でフィルタリングする場合は、配列を使うこともできます。

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田勇気」「佐藤淳」「鈴木由美」でフィルタリングします。
※「山田勇気」「佐藤淳」「鈴木由美」の行が表示されます。

配列を使う場合は大切な約束事があります。
「AutoFilter」の引数に「Operator:=xlFilterValues」を入れるということです。
「xlFilterValues」はフィルターの値を指定するものなのですが、これを指定すると、配列の中身がフィルタリングの要素として解釈されるようになります。

f:id:arekoretips:20210606053554j:image:w400 — 1行目のB列を「山田勇気」「佐藤淳」「鈴木由美」でフィルタリング

Sub AUTOFIL_ARR()
'
    Dim keyArray(2) As String
    '
    keyArray(0) = "山田勇気"
    keyArray(1) = "佐藤淳"
    keyArray(2) = "鈴木由美"
    '
    '「成績表」シートの1行目のB列を「山田勇気」「佐藤淳」「鈴木由美」でフィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter Field:=2, Criteria1:=keyArray, Operator:=xlFilterValues
'
End Sub

「○○以外」でフィルタリング

「Criteria」は、なにも指定しなければ自動的に「＝」として指定された情報を処理してくれますが、こちらで処理の方法を指定することもできます。

<>〇〇	〇〇以外
<>〇〇	〇〇を含まない
>〇〇	〇〇より大きい（〇〇を含まない）
>=〇〇	〇〇以上（〇〇を含む）
<〇〇	〇〇より小さい（〇〇を含まない）
<=〇〇	〇〇以下（〇〇を含む）

複数条件のフィルタリングと組み合わせて、「年齢が30歳以上且つ、40歳未満」といった絞り込みも可能です。

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田勇気以外」でフィルタリングします。
※「山田勇気」の行は表示されません（「山田勉」は表示されます）。

f:id:arekoretips:20210606053810j:image:w400 — 1行目のB列を「山田勇気以外」でフィルタリング

Sub AUTOFIL_OTHER()
'
    Dim keyWord As String
    '
    keyWord = "山田勇気"
    '
    '「成績表」シートの1行目のB列を「山田勇気以外」でフィルタリングする
    Worksheets("成績表").Rows(1).AutoFilter Field:=2, Criteria1:="<>" & keyWord
'
End Sub

複数列のフィルタリング

複数の列でフィルタリングしたい場合は、「With」を使って2つの「AutoFilter」を繋げてしまうと簡単にできます。

「成績表」シートの1行目（Rows(1)）のB列（Field:=2）を「山田勇気」で、C列（Field:=3）を「商品A」でフィルタリングします。
※「山田勇気」の「商品A」の行のみが表示されます。

f:id:arekoretips:20210606054357j:image:w400 — 1行目のB列を「山田勇気」で、C列を「商品A」でフィルタリング

Sub AUTOFIL_COL()
'
    Dim keyWord As String
    Dim keyWord2 As String
    '
    keyWord = "山田勇気"
    keyWord2 = "商品A"
    '
    '「成績表」シートの1行目のB列を「山田勇気」でフィルタリングする
    '「成績表」シートの1行目のC列を「商品A」でフィルタリングする
     With Worksheets("成績表").Rows(1)
        .AutoFilter Field:=2, Criteria1:=keyWord
        .AutoFilter Field:=3, Criteria1:=keyWord2
     End With
'
End Sub

フィルタリングを解除

設定しているフィルタリングを解除します。
フィルタリングされていない状態で実行しても、変化はありません。

Sub AUTOFIL_ALL()
'
    '全選択の状態にする
    If Worksheets("成績表").FilterMode Then Worksheets("成績表").ShowAllData
'
End Sub

まとめ

「Field:=」や「Criteria1:=」は省略可能

「*」でワイルドカード

「Operator」に「xlAnd」や「xlOr」を指定することでAnd検索やOr検索が可能

条件に配列が使える（「Operator:=xlFilterValues」を忘れずに）

「〇〇以外」「〇〇以上」などの条件の指定が可能

複数の列でフィルタリングしたい場合は、「With」を使って「AutoFilter」を繋げると楽

以上です。

技術tips保管庫

Excel VBAを中心に、仕事で役立つあれこれを記録