カテゴリー: フィルタ・検索・テーブル関連操作

コードコピー

'テーブル内にアクティブセルがあればTrueを返す
Debug.Print Sheet2.ListObjects(1).Active

テーブルのアクティブ状態を取得するには「Active」プロパティを使用します

テーブル機能を利用したExcelにおいて、そのテーブル内にアクティブセルが存在するかを判定基準とするプロパティです
これは単純にテーブル自体のアクティブ状態を取得することで、アクティブセルが含まれているかを判定することが出来ます

リボンにテーブル機能がのタブが表示されていれば、アクティブ状態で表示されていなければ非アクティブ状態です
リファレンスにもありますが、テーブル範囲のオブジェクトにはアクティブにするメソッドがありません
そのため、テーブルをアクティブ状態にするには範囲内セルを選択状態にする必要がありますが、そのセルが本当にテーブル範囲なのかどうかはこのプロパティを利用しないと分からないということです

Excelはユーザーが簡単に変更可能であることと、テーブル機能自体が左上のセルがA1に確定しているわけでも無いし、シート内に複数のテーブルを作成することも可能です
行列を追加挿入されてもテーブル機能としては一切問題が無いのです
そのため、状況によってはこのプロパティを使用する必要が出てくることもあります

画像の動きを確認してください
最初に取得をしているB3セルは範囲内なのでTrueを返しています

そして、2つ目の取得では見出しのセルが選択された状態です
当然見出しもテーブル範囲なのでTrueが返されます
画像にはありませんが、総計行が選択されていてもTrueが返されます
データ範囲のみだけが対象ではない点には注意が必要です

3つ目の取得は範囲外なのでFalseが返されています

また4つ目の取得でも注意が必要です
このプロパティはあくまでもアクティブ状態の判定のために、テーブル範囲内にアクティブセルがあるかどうかを判定します
つまり、選択範囲全てがテーブル範囲内にあるとは限りません

実際このプロパティの使用範囲は狭いとは思いますが、シート内に複数のテーブルを作成しているときなどに利用してください

テーブル機能は範囲を指定することが容易なので、シートの最下行や指定の列を必死に探していたころより遥かにデータの挿入が楽になりました
なので、最近よく使用する機能なのですが、つい先日ハマってしまったエラーに関する内容を記事にしておきますので、同じような処理を検討している場合は注意してください

結果から言いますと、テーブル内にテーブルの列名等を使用した数式を一括出力するとエラーが発生します
これはテーブルの列名等の数式は見た目は全く同じ数式になるのですが、その内部にはテーブルの指定セルが含まれているようです
そのため、テーブルに一括出力する際にまだ行作成が行われる前にその参照をしてしまいセルの未存在の実行時エラーが発生します

この画像の動きを確認してください
この処理では選択範囲のセルデータを配列に代入させて、その配列データをテーブルに一括出力している動きです
配列データの3列目は、1列目と2列目の値の一致判定を行う数式が入力されています
画像を確認してもらえばわかるように、出力する数式文字列は全て全く同じです

しかし、上述のようにこの数式が入力されて再計算が行われる方がテーブルの行作成より先に行われています
そのため、出力時に「実行時エラー ‘1004’:アプリケーション定義またはオブジェクト定義のエラーです。」が発生します

画像の出力後のデータを確認してもらうと分かるように2行目の数式セルまでは入力が行われています
2行目の数式セルを入力しようとしてエラーになっていることが、この結果からもわかります

さらにこのエラーの発生後に、なぞの現象として1行目のデータが無いことになってしまっています

正常に出力されているはずの1行目が、なぜか存在しないことになって削除が実行できなくなっています
もちろんデータが無いことになっているので、この状態でテーブルのオブジェクトのListObject.DataBodyRangeプロパティでデータを削除しようとするとエラーが発生します
データが無いため、DataBodyRangeプロパティが存在しないためです

この場合は、このプロパティを使用せずに処理を組んでおくか、もしくは手動操作にて行の挿入を一旦行ってください
すると空白行が1行目に挿入されて表示されているデータは消滅します
とはいえこのエラー対策を行っておけば、ここは必要ありません

解決策として

この場合の解決策は非常に単純で、行を作成してから数式を出力するようにすればいいだけになります

この場合の方法としては2つあると思います
ListRowsオブジェクトのAddメソッドで必要行数の空白行を作成してから配列を出力する方法と、そもそも数式を配列に含めず後で入力する方法です

テーブルの既定の動作として、数式を1行目に入力すると列全体に反映されます
つまり1セルだけ数式を入力すれば良いだけなのです
なので、後者の方法の方が圧倒的に簡単です
前者の方法はAddメソッドがあまり早くないので、どうしても必要な場合に選択してください

今度は配列に代入させる選択範囲は2列目までの範囲としています
その範囲の配列データをテーブルに一括出力します

その後3列目のデータ行1行目のセルに数式を入力しています
ただこれだけで3列目は数式が列全体に反映されます

このエラーはかなり苦戦しました
なんせ、出力先のテーブルの範囲も正常にAddressプロパティで取得できるし、配列データもテーブル以外の範囲に出力させると問題なく出力されます
配列データもそれなりの列数があったので、細かく1列ごとに確認をしていない状態でとにかく出力先のセル指定が間違っているのだろうとばかり思っていました

何よりこの処理を作成する直前に、全く同じ処理の流れで別の配列データを出力させていてそちらは全く問題なく、コードの流れも同じだったのも混乱を招きました
もちろん直前の配列には数式は含まれていませんでした

原因が分かってしまえば、対策にしてもどうと言うことは無いのですが、Web検索でエラー内容の検索をしてもセルの指定間違いという内容が多かったので自分で気づいたのは翌日のことでした

数式の計算が行の作成より先というのは盲点でした
ちなみにApplication.Calculationで計算を手動にしてもエラーを回避できませんので悪しからず～

コードコピー

'1列目のフィルタ条件を解除
Range("A1").AutoFilter 1

フィルタをかけた列の条件を列ごとに解除するには「AutoFilter」メソッドの条件を省略して実行します

画像の動きの様にメソッドに指定列数を指定するだけで実行すると、その列のみを解除します

この動きはフィルタがかかっているかいないかは関係なく動作するのですが、逆にフィルタがかかっているものを解除する、という動きにするにはフィルタの状態を判定する必要があります

コードコピー

'表範囲の1列目にフィルタをかける
Range("A1").AutoFilter 1, "=条件"

ワークシートの表範囲にフィルタの条件を指定して抽出するには「AutoFilter」メソッドを使用します

Mac版ではサポートされていないため使用できないようです
以下のページにて記載されています
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter（外部リンク）

このメソッドでは、Excel2007以降で機能がかなり追加されたため引数による柔軟な抽出がより行いやすくなりました

その中でも基本の単独列に単独の条件を指定する方法がこの例コードになります

今回使用する引数は2つです

「Field」はフィルタをかける表範囲の中での列数を数値で指定します
ワークシートの列数では無い点に注意が必要です

「Criteria1」はフィルタをかける際の条件になります
文字列や数値日付データなどで指定をすることが出来ます
またワイルドカードによる部分一致も可能です

Operator以下は今回は割愛します

実際の動き

画像の動きを確認してください
今回は2回フィルタをかけています

1回目が1列目に最初に「001」を含むものを指定しています
ワイルドカードによる指定が行えます
なので、「001-01,001-02,001-03」の3つが抽出されています

2回目が2列目に「対象３」を指定しています
これは1つしかデータは存在しないのでそのデータ行のみが抽出されます

条件に関して、1回目と2回目で見比べてもらうと分かる通り、最初に比較演算子を入れますが、省略も可能です
省略した場合は「=」を指定したときと同じ動きをしていることが確認できます
ですが、あまり良い使い方とはいえないかもしれませんので基本的にはちゃんと記載するようにしてください

また途中で解説したように、指定フィールド数はワークシートの列数とは一致しませんので、画像のような体裁のために空白列・行があるような場合は特に注意が必要です
画像を確認してもらうと分かりますが、1列目で変更されているのはB列なのでワークシート上では2列目になります

1列や2列であれば、簡単に判別できますが、これが数十列・数百列となってくると判別するのは困難になります
操作する表の上・左に空白行がある場合は要注意です

また気づいた方もいるかもしれませんが、このメソッドオブジェクトがほぼ意味はありません
表範囲に無いA1セルに対してメソッドを実行しているのに全く問題ありません
もちろんこんな書き方は推奨しません

このメソッドをマクロの記録によりコード作成すると表範囲が指定されています
これを行う必要はありません、そもそもこのメソッド自体がRangeに対してのメソッドなのも不思議なのですが、フィルタ機能はワークシートにつき1つしか作成されません

つまり、このセルを含むワークシートに対してフィルタを実行しているので問題が発生していないわけです
しかし全くの無意味でもありません、フィルタの矢印を設定する範囲を指定することが出来るからです

なので、それを設定する段階ではない画像のような場面ではまったく問題が起きていないのです
問題ないからそれでいいや、とは言わずにせめて表範囲の始点セルくらいは指定するようにしてください

コードコピー

'アクティブシートのフィルタ設定状態を取得
Debug.Print ActiveSheet.AutoFilterMode

シートにフィルタが設定適用されているかどうかを調べるには「AutoFilterMode」プロパティを使用します
このプロパティがTrueを返すと適用されており、Falseが返されると適用されていない状態となります

基本的には、フィルタの適用を解除したい場合に解除する前に適用されているかを調べる時に使用します

ちなみに、このプロパティでは設定を行うことも可能ですがFalseだけです
Trueを設定すると実行時エラーが発生します
Falseを代入するとフィルタの適用を解除できます
なので、こちらで解除を行えば十分に感じるかもしれませんがExcelでよく使用されるテーブル使用時に注意点が出てきます

テーブル使用時の注意点

このプロパティはWorksheetオブジェクトのメンバーです
対してテーブルはListObjectというオブジェクトになります
そのため、このプロパティが無効となってしまいます

画像を確認してください
この画像ではコードもデータ内容も全く同じものを使用しています

シートのデータ範囲がテーブルに変換されている点が違います

ですが、こちらではフィルタの矢印が表示されていても、いなくてもFalseが返されています
これがこのプロパティでの大きな注意点です

フィルタをかけるメソッド自体は、テーブルかどうかに関わらず使用できます
なので非適用の状態からフィルタを適用する状態にすることは出来ています

ですが、このプロパティは無効となってしまいます
そのため、上記で解説したFalseを設定することによるフィルタの適用解除も使用できません

フィルタの操作自体はテーブルに関わらないので、問題になることはありませんが
AutoFilterModeプロパティ使用時はテーブルの存在にも注意してください

コードコピー

'フィルタが適用されていれば解除する
If ActiveSheet.AutoFilterMode = True Then
Cells.AutoFilter
Else: End If

ワークシートのフィルタを解除するには「AutoFilter」メソッドを引数を全て省略して使用します

ただこのメソッドはプロパティでは無いため、設定の適用・解除という操作が行えず、実行するたびに適用・解除が切り替わります
そのため、このメソッドを使用して解除する場合は事前にフィルタが適用されているかを調べておく必要があります

これを調べてから実行しないと、解除したいのに元々未適用であれば、適用状態になってしまいます

フィルタの適用設定に関しては、フィルタを指定する際にもこのメソッドを使用するので、未適用状態でフィルタを実行すれば、フィルタの適用と同時にフィルタもかかるので適用する場合にはあまり適用状態を気にする必要はありません
解除する場合のみ例コードの様に判定を行ってください

ちなみに、フィルタを解除するとかかっているフィルタも全て解除されるので、非表示になっているデータは全て表示されます

コードコピー

'フィルタ結果件数取得
Debug.Print Application.WorksheetFunction.Subtotal(103, Range("A:A")) - 1

ワークシート上でフィルタ機能を使用して、抽出されたデータの件数を取得します
このコードを実行すると、件数を整数値として取得出来ます

そもそもフィルタとは、Excelの標準機能の１つです
特定の列のデータの中から指定の条件に一致するデータだけを表示させて、一致しないものは行が非表示になります

そして上記にもあるように、これは標準機能です
なのに、結果の件数を取得するプロパティやメソッドはありません
自分で数える必要があります

この画像はフィルタをかけた状態です
画像下部の赤く色の付いた箇所にフィルタの結果件数が表示されています
この数字が欲しい訳です

ここにあるんやから、プロパティとかで簡単に取得出来そうな感じがするのに何でかありません

件数の求め方は、２通りあります
１つは記事コードのワークシート関数を使用する方法
もう１つは表示・非表示のセル個数を数えて求める方法です

結果は当然同じになるので、どっちでもええと思います
ただ後者の方法は回りクドいコードになってしまうので、ワークシート関数を使用する方が簡単です

コード解説

Debug.Print Application.WorksheetFunction.Subtotal(103, Range("A:A")) - 1

コード解説というか、これは関数解説にあたるかもしれません

ただ単にワークシート関数のSUBTOTAL関数を使用しているだけです
なので、この関数について少し解説をします

この関数は、小計関数とも呼ばれている
小計計算に適した関数です

さらに特殊なのが、引数1つ目の設定で色々な計算方法を選択することが出来る点です

今回はその中で、COUNTA関数の計算方式を使用します
COUNTA関数は空白ではないセル個数を取得する関数です

その指定箇所が、引数1つ目の103という数値の部分です
この103は、COUNTA関数の計算方法で、かつ非表示行を計算に含まない指定になります

これで、表示されたデータのあるセルの個数が取得されます

～～ - 1

ここで取得したデータのあるセルというのは、当然見出し部分も含んでいます
なので、この取得したセルの行数から見出し行の数だけマイナスすればフィルタの結果件数を取得することが出来ます

見出しの行数が2行ある場合は、「-1」を「-2」に変更するようにしてください

ただこれだけで良いのですが、プロパティがあったらええのになぁ、と感じます

何故なら、このフィルタをかけて何らかの処理を行う場合にはほぼ必須のコードだからです
フィルタの結果件数が0になる可能性は自動処理では往々にしてあります

この状態で抽出されたデータをコピペする場合、なにも範囲指定されないだけならまだしも、使用するプロパティ次第では見出し方向に指定されて見出しがコピペされてしまったりします

そもそも条件に一致するものが無いということ自体をユーザーに知らせる必要がある場合もありますので、フィルタをVBAでかける際はきっちり判定を行うようにしましょう

引数の「103」について

引数1つ目は計算方法の指定と解説しました、103という数値が表示された行のCOUNTA関数になります
これを「3」と設定した場合は、表示・非表示は考慮しなくなりますので、非表示の行のデータの個数も数えられます

関数のリファレンスにも記載はあるのですが、このSUBTOTAL関数でどちらを設定しても、フィルタにより非表示になっている行は除外されます

なので、フィルタ件数のカウントに関してはどちらでも構わない訳です
ですが記事コードでは非表示を含まないように指定しています

フィルタを使用せずに、ユーザーが非表示にした行数を数えたいことも過去にあったのでどちらにも対応できるようにしたかったからです

フィルタがかかっていない状態で、単にユーザーが行の非表示をした場合でこの「3」か「103」かで差が発生します

フィルタをかけずに、ユーザーが2行非表示にした場合にこの設定次第でその2行の差がカウント数に出ます
当然と言えば、当然ですよね
非表示を含むか含まないかの設定なんだから

まあ、そのユーザー操作の方も非表示を除外したい場合は「103」にするわけです

ただ、ここで1つ仕様上の注意点があります
そしてこれこそが記事コードの弱点でもあります

この画像を確認してください
特に右下のレコード数の表示の部分に注目してください

「5レコード中4個が見つかり！」と、途中で切れてまるでアニメの萌えキャラの独特な語尾のようになっていますが、それはさておき実際にシート上に表示されているデータ行数と一致していません

これは「フィルタをかけたうえで手動で1行非表示にする」というとんでもなく意地悪な操作を行っているためです
それは手動で行わず、フィルタで行ってくれよ！と言いたいところですが、こんな意地悪な状況でも関数はうまく答えています

データ個数「3」が出力されています
そして、今回は引数1つ目を「3」と「103」の両方を使用していますが、どちらも同じ数値を取得しています

と、ここで不思議な話です
フィルタをかけて非表示になった行に関しては、設定の違いに関係なく非表示行は無視される、と上記でも解説しました

ですが、今回はフィルタではなく手動による非表示行があります
実際、ステータスバーの文字列のカウントが4個となっていることからもフィルタとしての非表示と違うのが分かります

これで分かるのは、ステータスバーでの個数の数え方が根本的に表示だとか非表示だとかではなく、条件に一致するものを数えているのだということです

つまり、このステータスバーの数値が簡単にプロパティで取得できればええのに～と言いましたがこの数値がそのまま表示されたデータ個数にはならないことがあるため、プロパティが無いのかもしれません

開発者の方々のデバッグ力の凄まじさが良く分かるし
やっぱりVBAって、良く考えられてる言語やな～

コードコピー

'フィルタの条件設定をクリアする
ActiveSheet.ShowAllData

コードコピー

'フィルタの条件設定をクリアする
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

フィルタのどれかの列でソートがかかっている状態を一括で全解除するには「ShowAllData」メソッドを使用します

このメソッドは、フィルタがかかっていない状態で実行するとエラーが発生します
なので、処理の最初に初期化しておきたいような場合はエラーを無視して実行します

フィルターが設定されているかは調べればわかりますが、あまり意味は無いと思います

また、ここでいう解除とはフィルタ機能の解除ではありません
ソート条件の解除になりますので、注意してください

フィルタを処理中にかけなおす場合は、当然エラーにはなりませんので1つ目のエラー無視は入れずに実行するといいです

ユーザーがフィルタをかけて保存していると、当然処理がうまくいかない可能性がありますのでフィルタのあるシートを処理する場合は最初に入れておくと安心です

ちなみに、昇順降順の矢印の表示もクリアされますがその設定は解除されたりはしません

Worksheets(1).ShowAllData

またこのメソッドはシートに対して行いますので、このコードのようにワークシートを指定して、別のシートのフィルタ条件を解除することもできます