2022/09/06

ListObjectの絞り込み解除

ListObjectについて、下記のようなシリーズで説明しています。
　・ListObjectの作成と概要
　・ListObjectの並べ替え
　・ListObjectの絞り込み
　・ListObjectの絞り込み解除　　←今回
　・ListObjectの絞り込みデータの配列化
　・ListObjectのデータ追加
　・ListObjectのデータ変更
　・ListObjectのデータ削除

今回は、ListObjectオブジェクトの「絞り込み解除」について説明します。

１．手動でのテーブルの絞り込み解除

まず手動で絞り込みを解除する手順を確認します。
通常は図１のように、絞り込みを解除したい列の「フィルターボタン①」をクリックします。フィルターボタンは絞り込み等がされていない場合は、下三角形「」の形をしていますが、絞り込みを行っている列ではロートの付いた「」形になります。
（なお、昇順並び替え＋絞り込みの場合は「」、また降順並び替え＋絞り込みの場合は「」の形となっています。）


図１別窓

絞り込みの「フィルターボタン①」をクリックするとメニューが現れ、その下の方に「（すべて選択）」に続いたリスト項目のチェックボックスが並んでいます。その「（すべて選択）」のチェックボックスにチェック②を入れると、全てのチェックボックスにチェックが入り③、OKボタンをクリック④することで絞り込みが解除⑤されます。

また図２のように、テーブルの一部をセル選択⑥し、リボンの「データ」タブ→「並べ替えとフィルター」グループ→「クリア⑦」をクリックすることで、テーブルの全列の絞り込み解除がまとめて行えます。但し、この操作では「並べ替えも解除」されてしまうので注意が必要です。

図２別窓

２．マクロでのテーブルの絞り込み解除

絞り込みをVBA側から解除するには、以下の４種類の方法がありそうです。

1. [テーブル].Range.AutoFilter Field:=列位置 …… Fieldパラメータのみを指定して１列ずつ解除
2. [テーブル].Range.AutoFilter …… 全パラメータを指定せずにフィルタ―ボタンを消去・再表示
3. [テーブル].ShowAutoFilter …… False/True設定でフィルタ―ボタンを消去・再表示
4. [テーブル].AutoFilter.ShowAllData …… 全データを表示（＝解除）

２ー１．１列ずつ解除

他のサイトでも多く紹介されているのが、AutoFilterメソッドにFieldパラメータのみを指定することで「指定した列の絞り込みを解除」させる方法です。列が絞り込まれている状態か否かは「AutoFilter.Filters(列位置).Onプロパティ」の値を調べます。
絞り込み解除の実行プロシージャを図３に示します。引数として対象のListObjectオブジェクトを指定します。

0. '==========　⇩(1)　１列ずつ解除　============
1. Sub TableFilterOff_01(T As ListObject)
2. 　Dim i As Integer 　　 '←カウンタ変数（列位置）
4. 　If T.ShowAutoFilter = False Then
5. 　　T.ShowAutoFilter = True
6. 　　Exit Sub
7. 　End If
9. 　For i = 1 To T.ListColumns.Count
10. 　　If T.AutoFilter.Filters(i).On = True Then
11. 　　　T.Range.AutoFilter Field:=i
12. 　　End If
13. 　Next i
15. End Sub

図３別窓

０９行目「For i = 1 To T.ListColumns.Count」で、カウンタ変数ｉをテーブルの列数分だけ回して、１列ずつ処理を行います。「T.ListColumns.Count」の代わりに「T.HeaderRowRange.Count」や「T.AutoFilter.Filters.Count」でも同じようにテーブルの列数が得られます。
１０行目「If T.AutoFilter.Filters(i).On = True Then」では、その対象の列が「絞り込まれているか否か」を調べます。
「テーブル.AutoFilter.Filter」には、図４のようなプロパティがありますので、その内の「Onプロパティ」を使用しています。

AutoFilter.Filterオブジェクトのプロパティ（一部）

プロパティ	内容
Count	Filters.Countとすることで､ﾌｨﾙﾀの数を取得
Criteria1	絞り込み時のCriteria1の条件式を取得 絞り込んでいない列ではｴﾗｰ発生
Criteria2	絞り込み時のCriteria2の条件式を取得 Criteria2が無い時や､絞り込んでいない列ではｴﾗｰ発生
On	絞り込みしている列はTrue
Operator	絞り込み時のOperatorの値 Criteria1のみの単純な絞り込みの時はｾﾞﾛ値

図４別窓

１０行目が成立した「絞り込まれている列（=True）」の場合は、１１行目「T.Range.AutoFilter Field:=i」と、AutoFilterメソッドにFieldパラメータのみを指定（＝Criteria1を指定していないため、全ての値を指定したことになる）することで「指定した列の絞り込みを解除」します。

但し０９～１３行目が成立するのは「フィルターボタンが表示されている」場合です。フィルターボタンが表示されていない状態で実行されると、１０行目「T.AutoFilter.Filters(i).On」のOnプロパティ値を取得する時にエラーが発生します。
そのため０４行目「If T.ShowAutoFilter = False Then」で、フィルターボタンが非表示の時には０５行目「T.ShowAutoFilter = True」でフィルターボタンを表示させ、０６行目「Exit Sub」で実行プロシージャを抜け出します。もともとフィルターボタンが無く「絞り込みがされていない状態」なので、フィルターボタンを表示するだけで終了してしまってもOKだからです。

なお１１行目「T.Range.AutoFilter Field:=i」では、フィルターボタンが無くてもエラーは発生せず、「フィルターボタンを再表示」するだけ　という動作となります。ですので、０４～０７行目のフィルターボタン有無での処理、及び１０行目の絞り込み有無の判断を無くした図５のようなプロシージャでもOKです。
但し、全ての列に対して（絞り込みがされていても、いなくても）絞り込み解除の処理をしますので、処理時間は他のコードよりも多くかかります。

20. '==========　⇩(2)　絞り込まれていなくても1列ずつ解除　============
21. Sub TableFilterOff_02(T As ListObject)
22. 　Dim i As Integer 　　 '←カウンタ変数（列位置）
24. 　For i = 1 To T.ListColumns.Count
25. 　　T.Range.AutoFilter Field:=i
26. 　Next i
28. End Sub

図５別窓

２ー２．AutoFilterメソッドでボタン消去・再表示

AutoFilterメソッドをパラメータ無しで実行する方法が図６です。

40. '==========　⇩(3)　AutoFilterメソッドでボタン消去・再表示　============
41. Sub TableFilterOff_03(T As ListObject)
43. 　If Not T.AutoFilter Is Nothing Then
44. 　　T.Range.AutoFilter
45. 　End If
47. 　T.Range.AutoFilter
49. End Sub

図６別窓

４４行目・４７行目の「T.Range.AutoFilter」は、実行されるたびに「フィルターボタンを消去・表示」を繰り返します。ですので、フィルターボタンが表示されている状態から「AutoFilter 実行」→「AutoFilter 実行」と２回繰り返せば、フィルターボタン表示→「消去」→「再表示」となり、「表示→消去」の時に絞り込みが解除されることになります。

ただし初期状態が「フィルターボタン無し」の状態から２回「AutoFilter 実行」を繰り返してしまうと、最後が「消去」の状態となってしまいます。フィルターボタンが無い状態は「テーブル.AutoFilter ＝ Nothing」で判断できますので、４３行目「If Not T.AutoFilter Is Nothing Then」で、フィルターが存在する場合だけ２回（フィルターが無い状態では１回のみ）繰り返すようにしています。

２ー３．ShowAutoFilterプロパティでボタン消去・再表示

テーブルのShowAutoFilterプロパティを操作し「フィルターボタンを消去・再表示」させて絞り込みを解除させるのが図７です。

60. '==========　⇩(4)　ShowAutoFilterプロパティでボタン消去・再表示　============
61. Sub TableFilterOff_04(T As ListObject)
63. 　If T.ShowAutoFilter = True Then
64. 　　T.ShowAutoFilter = False
65. 　End If
67. 　T.ShowAutoFilter = True
69. End Sub

図７別窓

「テーブル.ShowAutoFilter」は状態取得／設定の両方が可能なプロパティで、取得時にTrue値ならば「フィルターボタンが表示」されている事を表しています。またTrueに設定するとフィルターボタンが表示され、Falseに設定すると消去されます。
ですので「フィルターボタンが表示されている状態」→「False設定でフィルターボタン消去」→「True設定で再表示」すれば、表示→消去の段階で絞り込みが解除されることになります。

但し、最初から「フィルターボタンが無い（＝絞り込みが既に解除されている）」状態ならば、False設定でボタンを消去する必要はありません。ですので６３行目「If T.ShowAutoFilter = True Then」で「フィルターボタンが存在している時」だけ６４行目「T.ShowAutoFilter = False」で「（絞り込みの有無にかかわらずに）フィルターボタンを消去」し、６７行目「T.ShowAutoFilter = True」でフィルターボタンを再表示しています。
なお、フィルターボタンの存在有無を確認する方法として今回の「ShowAutoFilterプロパティ」を使う以外に、図６の４３行目のような「If Not T.AutoFilter Is Nothing Then」の方法も使えます。

２ー４．ShowAllDataで全データを表示

ShowAllDataメソッドを使い、全データを表示することで絞り込みを解除する方法が図８です。

80. '==========　⇩(5)　ShowAllDataで全データを表示　============
81. Sub TableFilterOff_05(T As ListObject)
83. 　If T.AutoFilter Is Nothing Then
84. 　　T.Range.AutoFilter
85. 　　Exit Sub
86. 　End If
88. 　If T.AutoFilter.FilterMode = True Then
89. 　　T.AutoFilter.ShowAllData
90. 　End If
92. End Sub

図８別窓

フィルターボタンが存在する時には、８９行目「T.AutoFilter.ShowAllData」で全データを表示させて「絞り込みを解除」させます。なお絞り込まれていない場合は無駄になりますので、８８行目「If T.AutoFilter.FilterMode = True Then」で絞り込まれている場合にのみ実行するようにしています。

但しフィルターボタンが無い場合は、８８行目・８９行目ともエラーが発生してしまいますので、８３～８６行目で「フィルターボタンが存在しない（＝絞り込みも解除済み）場合は、フィルターボタンを表示させて終了」するようにしています。
８３行目「If T.AutoFilter Is Nothing Then」でフィルターボタンの存在を確認し、存在しない場合は８４行目「T.Range.AutoFilter」でフィルターボタンを表示させて、８５行目「Exit Sub」で制御を呼び出し側に戻しています。
なお、図３の０４～０７行目のようなコードを使ってもOKです。

他のサイトでは、ワークシート（ActiveSheet）に対してFilterModeプロパティを使用して絞り込み有無を確認し、ShowAllDataで絞り込みを解除する手法を良く紹介しています。FilterMode・ShowAllDataはWorksheetクラスだけでは無く、AutoFilterクラスにも存在します。但し微妙に仕様が異なるので、その比較を図９にまとめました。
今回はAutoFilter側を使用しています。セル選択位置に影響されないという特性からもAutoFilter側の方が使い易い気がしますが、それぞれの特徴を把握した上で使って下さい。

			ActiveSheet	[ﾃｰﾌﾞﾙ].AutoFilter
ﾌｨﾙﾀｰ ﾎﾞﾀﾝ有	FilterMode プロパティ	手動 絞込	選択ｾﾙのﾃｰﾌﾞﾙが絞り込まれていればTrue､ 並べ替えのみではFalse｡ ﾃｰﾌﾞﾙ以外を選択時はFalse	対象ﾃｰﾌﾞﾙが絞り込まれていればTrue 並べ替えのみではFalse
		VBA 絞込	ｼｰﾄ上の1ﾃｰﾌﾞﾙが絞り込まれていれば､どこを選択中でもTrue｡ 但し､選択領域を移動(ﾃｰﾌﾞﾙ⇔ﾃｰﾌﾞﾙ以外､ﾃｰﾌﾞﾙ→異なるﾃｰﾌﾞﾙ)すると､手動絞込と同状態になる｡ また並べ替えのみではFalse
	ShowAllData メソッド		絞り込み＋並べ替えが解除｡ ActiveSheetのFilterModeがFalseの時はｴﾗｰ発生	絞り込みが解除されるが､並べ替えは解除されず｡ ﾃｰﾌﾞﾙのFilterModeがTrue/Falseどちらでもｴﾗｰは発生せず
ﾌｨﾙﾀｰ ﾎﾞﾀﾝ無	FilterMode プロパティ		False	エラー
	ShowAllData メソッド		エラー	エラー

参考サイト「Power PlatformとExcelの技術ブログ　パパセンセイ365」
図９別窓

アプリ実例

「DVD等の内容・保管場所等管理システム」
「先行予約可能な備品予約・貸出システム」
「ToDoリストで個人タスク管理」
「会社番号検索システム」