使用環境: MS Office Excel 2010
VBA で Excel のオートフィルターを操作する方法について調べる機会があったので、その内容をここにまとめておく。
(基本的な使い方はぐぐったり Microsoft の公式ページを読んだりすればわかるので、ここではより細かな点について触れる)
オートフィルターを設定する
オートフィルターを設定するには、Range オブジェクトの AutoFilter メソッドを使う。
(レシーバーとなる Range オブジェクトの選定方法については後述。ここではとりあえず表の左上セルとしておく)
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=">10"
ただこれだけだと、ワークシート内で既にオートフィルターが設定されている場合に問題が起こる。
指定した Range オブジェクトが無視され、設定済みのオートフィルターでフィルタリングが行われてしまう。
こういう場面にも対応するには、事前に「オートフィルターを解除する」コードを忍ばせておく。
With ActiveSheet
.AutoFilterMode = False ' オートフィルターが設定されていた場合、解除する
.Range("A1").AutoFilter Field:=1, Criteria1:=">10"
End With
オートフィルターの範囲
先ほどの例では、表の左上のセル(A1)を指定して AutoFilter メソッドを呼び出していた。
そうすると、結果的に A1:B14 の範囲がフィルタリングされるのであった。
これは、表内の別のセル(たとえばB7とか)を指定しても同じである。
この挙動は「データ範囲の自動認識」によるもの。
セルの書式とか、セルに値が入っているかどうかなどを見て自動的にどこからどこまでが表かを判断してくれている。
詳しい仕様は以下のページを参照
https://support.microsoft.com/ja-jp/help/814229
http://www.eurus.dti.ne.jp/~yoneyama/Excel/yakusoku/range.html
「データ範囲の自動認識」は、セルを１つだけ選択してオートフィルターを適用した場合にのみ働き、セルを２つ以上選択してオートフィルターを適用した場合は働かない。
With ActiveSheet
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=1, Criteria1:=">10"
End With
With ActiveSheet
.AutoFilterMode = False
.Range("A1:B1").AutoFilter Field:=1, Criteria1:=">10"
End With
オートフィルターの条件
オートフィルターの条件(Criteria1, Criteria2)には、以下6種類の比較方法を指定できる。
| 1. xx と等しい | "=xx" |
| 2. xx と等しくない |
"<>xx" |
| 3. xx より大きい |
">xx" |
| 4. xx 以上 |
">=xx" |
| 5. xx より小さい |
"<xx" |
| 6. xx 以下 |
"<=xx" |
また、ワイルドカードと組み合わせて以下のような条件も表現できる。
| 7. xx で始まる | "=xx*" |
| 8. xx で始まらない |
"<>xx*" |
| 9. xx で終わる |
">*xx" |
| 10. xx で終わらない |
">=*xx" |
| 11. xx を含む |
"<*xx*" |
| 12. xx を含まない |
"<=*xx*" |
これはおそらく、COUNTIF 関数の条件式に指定できる内容と同じ。
=COUNTIF(A1:A30, ">30") ←これ
ところで、少し気になったことがあったので実験。
かなりわざとらしいが、以下のようなデータを用意（ちなみに、昇順にソートしてある）。
これを、(まずはVBAを使わず手動で)「=b（イコール・ビー）」「より大きい」でフィルタリングしてみる。
予想では、昇順に並べて「=b」「より大きい」データ…即ち「=c」「a」「b」「c」の４つが引っかかるはずである。
しかし実際は、「b」「c」の２つしか引っかからなかった。
フィルターオプションを開き直してみると、さっきと内容が違っている。
マクロの記録で VBA コードを吐かせてみると原因ははっきりしており、当初「=b」「より大きい(>)」のつもりで設定した条件が、実際は「b」「以上(>=)」と解釈されてしまうため。
| >=b |
「=b」「より大きい(>)」 |
|
| >=b | 「b」「以上(>=)」 | ←Excel による解釈はこっち |
エスケープする方法とかないのかと探してみたが見つけられず。
(まぁ、これができないからといって困る場面なんてそう多くはなさそうだが)
どうしてもこのような条件でフィルタリングしたければ、以下のように AND 条件を指定してやることになるか。
VBA で書くなら以下の通り。
With ActiveSheet
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=1, Criteria1:=">==b", Operator:=xlAnd, Criteria2:="<>=b"
End With
