知らずに落ちると抜け出せなくなるVBAの落とし穴です。
- オートフィルターが全く違う場所で実行された
- マクロを実行するたびに実行されるフィルターが変わる
あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。
Rangeオブジェクト.AutoFilterの仕様
例えば以下のシートで以下のコードを実行してみましょう。
Range("G3:J9").AutoFilter 1, "みかん"
結果はこうなります。
全然違う位置でフィルターがかかっていますし、
肝心のG列～J列にはフィルターが設置すらされていませんね。
タイトルの通り、既存のオートフィルターがある場合は、
AutoFilterメソッドをどのセルから実行してもそのフィルターで抽出が実行
されてしまいます。
以下のコードが「G3～J9セルにフィルターを設置するコード」になるのは、
フィルターがシート上に存在しない時だけですのでご注意ください。
Range("G3:J9").AutoFilter 1, "みかん"
解決策
フィルターの抽出と設置を同時にやるのは危険なため、
- フィルターがすでにある場合は解除
- フィルターを設置
- フィルター抽出を実行
という手順をしっかり踏みます。
具体的なコードとしては、
' フィルターがあればフィルターを解除 Worksheets("○○").AutoFilterMode = False ' フィルターを設置 Worksheets("○○").Range("G3:J9").AutoFilter ' 抽出を実行 Worksheets("○○").Cells.AutoFilter 1, "みかん"
この手順でコードを実行します。
Range.AutoFilterはトグル形式(設置と解除を交互に実行)のメソッドですが、
このような前後の状況で処理が変わるコードを使うとマクロが不安定になるため
Worksheets("○○").AutoFilterMode = False
このコードで「フィルターの解除」を明示しているのがポイントです。
このコードは解除済みであってもエラーにはなりませんので、
このコードの直後であれば確実にオートフィルターはありません。
その後にAutoFilterを実行すれば、確実に「設置」を実行できます。
あとは抽出ですが、逆にこの実行元はどのセルでも構わないため、
Worksheets("○○").Cells.AutoFilter 1, "みかん"
と、Cellsで済ませてしまってOKです。
仕様さえ知っていれば対策は簡単ですね。
なぜこの仕様なのか＋追加の注意点
なんでAutoFilterがこんな仕様になっているのかというと、
AutoFilterはテーブル(ListObject)と共存することがあるからです。
テーブル機能を使えばシート上に複数のフィルターを設置でき、
それぞれのフィルターを独自に動かせます。
その仕様の下で↓こちらのコードは、
Range("G3:J9").AutoFilter 1, "みかん"
- G3:J9にテーブルが存在すればそのフィルター
- そうでなければシート上の通常フィルター
を取得するコードとして動きます。
このためテーブルのないシートでは結果として、
「どこから実行しても既存のフィルターが使われるコード」
になったということですね。
ここで一つ注意点なのですが、先ほど
「このコードの直後であれば確実にオートフィルターはありません。」
と説明したのは、あくまで通常フィルターしかないシートの話です。
テーブルと通常フィルターが混在したシートではそうとは限らず、
かなり複雑な指定が必要になりますのでご注意ください。
本来あまりやるべきではありませんが、
必要になりましたら以下の記事をご参照ください。
www.limecode.jp
