(PR)　耳で聴く本【Amazon Audible】、詳細はこちら
プチIT化 PR

ShowAllDataができない、テーブルのフィルター解除方法

 
記事内に商品プロモーションを含む場合があります。
当サイトは、アフィリエイト広告を利用しています。

こんにちは！今回の記事は、Excel VBAマクロでのフィルター「ShowAllData」が上手くいかない、どうすれば良いか？という記事になります。とある日、マクロでフィルターを全解除しようとすると…

悩める人
悩める人
フィルター全解除実行しても、エラー出ないけど、解除もされず反応がない。なんで？！

と言う状況に陥りました。今回はShowAllDataでフィルター全解除しようとしたけど、反応がないのはなぜ？どうしたら？という事をまとめていきます。それでは、いってみましょう！

事象詳細：ShowAllDataが効かない

以下のように、フィルターモードがTrueであった場合、すべてのデータを表示するというマクロを実行していました。

If ActiveSheet.AutoFilterMode = True Then
  ActiveSheet.ShowAllData
End If

しかし、実行してもフィルターは解除されていない（条件で絞られたまま）で表示されているデータに変わりはなく、かといてエラーも出ないという状況でした。

原因：テーブルとして書式されている場合はフィルターの絞込みが無しと判定される

フィルターがかかっている範囲がテーブルとして書式設定されている場合、ShowAllDataが効かないようでした。具体的に言うと、フィルターの絞込み判定が「ない」と判定されてしまっていました。

試しに、フィルターの絞込みがあった場合には「絞込みあり」、無かった場合には「絞込みなし」とメッセージボックスを出すようなマクロで調査してみました。

If ActiveSheet.AutoFilterMode = True Then
  ActiveSheet.ShowAllData
  MsgBox ("絞込みあり")
Else
  MsgBox ("絞込みなし")
End If

実行結果としては、フィルターありで絞っているにもかかわらず、なしのメッセージボックスが出てきてフィルター絞込みなしと判定されてしまっていることが分かりました。

調べていくと、「オートフィルター」と「テーブルのフィルター」では扱いが違うので、明示的にテーブルを指定することが必要でした。テーブルのフィルターの場合の解除方法でVBAマクロを記載すれば解除できましたので、紹介していきますね。

その前に…テーブル定義されているかどうか確認するには？

疑問に思う人
疑問に思う人
そもそも、私のエクセルではテーブル定義されているのかな？

と思う方のために、確認手順を載せておきますね。

  1. フィルターの付いている表のどこかのセルを選択
  2. テーブルツールのメニューが上に出てきたらテーブル定義されている

テーブルの名はテーブルツールのデザインを選択した後、テーブル名のところを見れば確認できますよ！

対処：ListObjectsでテーブルに対して処理する

では、テーブル定義があってフィルターがShowAllDataで解除できない場合、どうすれば良いのかを確認していきましょう。

  • フィルターを1列ずつ解除する
  • フィルターを消してフィルターを付ける
  • フィルターを消す

という3つのサンプルマクロを紹介していきますね。

フィルターを1列ずつ解除する

フィルターの列を指定して解除する方法です。Fieldのあとで指定する数字がテーブルの中の列番号となります。（テーブルの左から1列目、2列目と数えます。）以下サンプルでは1列目と2列目を削除します。

ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=2

5列などある場合は以下のようなマクロになりますね。

ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=2
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=3
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=4
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=5

飛び飛びで解除したい場合、（テーブルの中のいくつかしかフィルターがかかっていない場合）列番号指定で解除すればOKです。1列目と3列目のフィルターを解除したい場合は、列1と3を指定します。

ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("テーブル2").Range.AutoFilter Field:=3

フィルターを消してフィルターを付ける

フィルターを1列ずつ指定して解除するのは面倒だから、一気にやりたいという方は、フィルターを一度なくしてもう一度付けることによって全クリアも実現できますよ。

ActiveSheet.ListObjects("テーブル2").ShowAutoFilter = False
ActiveSheet.ListObjects("テーブル2").ShowAutoFilter = True

フィルターをなくす

そもそもフィルター自体もう必要ないんだわ…という方は、フィルターをFalseにするという処理を入れればOKです。

ActiveSheet.ListObjects("テーブル名").ShowAutoFilter = False

さいごに

いかがだったでしょうか。同じような疑問を持たれている方の参考になれば幸いです。それでは、また！