none
Excelマクロでオートフィルタして件数を取得するとオーバーフローします RRS feed

  • 質問

  • こんにちは。
    Excel2016のマクロについて、お問い合わせさせてください。


    VBAで、オートフィルタを設定しています。
    1行目にはタイトル行で、2行目以降にデータがあります。
    AutoFilterでフィルターして、2行目だけデータが抽出された際に、
    Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).Countで件数を抽出しようとするとオーバーフローします。
    (別のファイルで違うデータで試した際は、2行目にデータが抽出しているにもかかわらず上記コマンドで0件となりました。)
    3行目以降にデータがある場合や、2行目にデータがあっても他の行でデータが抽出された場合は、この現象は発生しません。

    原因が不明な為、お問い合わせさせて頂きました。何かわかりましたら、ご教示頂きたく宜しくお願い致します。

    2019年6月26日 8:53

すべての返信

  • こんにちは。
    Excel2016のマクロについて、お問い合わせさせてください。


    VBAで、オートフィルタを設定しています。
    1行目にはタイトル行で、2行目以降にデータがあります。
    AutoFilterでフィルターして、2行目だけデータが抽出された際に、
    Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).Countで件数を抽出しようとするとオーバーフローします。
    (別のファイルで違うデータで試した際は、2行目にデータが抽出しているにもかかわらず上記コマンドで0件となりました。)
    3行目以降にデータがある場合や、2行目にデータがあっても他の行でデータが抽出された場合は、この現象は発生しません。

    原因が不明な為、お問い合わせさせて頂きました。何かわかりましたら、ご教示頂きたく宜しくお願い致します。

    取り敢えず、テストしていないので正常動作するか分かりませんが、
    上記のコードなら、

        Dim rng as Range
        Set rng = Range("A2").CurrentRegion
        Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
      Debug.Print rng.SpecialCells(xlCellTypeVisible).Count
    
    としたらどうなりますか?
    (上記のコードの場合、データが 1 つもない場合はエラーになります)

    2019年6月26日 12:58
  • kato_hiroさん、こんばんは。

    Windows10+Excel2016で動作確認したところ、どうやらAutoFilterで1つ又は複数の行が選択されているときに1つのセルのみ選んで
    Range(セルのアドレス).SpecialCells(xlCellTypeVisible).Count
    を実行するとオーバーフローのエラーになってしまう様です。

    次のURLでは、kato_hiroさんが提示されたコードと同じコードが示されているので、1つのセルのみ選んで実行したときにエラーになってしまうのが本来の仕様と言う訳ではなく、2013より後で、最近発生したバグかもしれませんね。
    https://www.moug.net/tech/exvba/0150043.html

    ちなみに、先ほどWindows7+Excel2013での動作確認をしたところ、何と!、オーバーフローのエラーになってしまいました。残念ながらExcel2013なら大丈夫、という訳ではないようです。

    フィルターが複数の列に対して設定されており各列に値が入っているのであれば、例えば
    Range(Range("A2"), Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible).Rows.Count
    として1レコードが選択されている場合でも複数セルのセル範囲を選択してエラーを回避して、作業を先に進めるのがよろしいように愚考しますが、ご参考まで。





    2019年6月26日 13:29
  • A1:A10にタイトル行とデータを入れて、フィルタにて2行目だけを表示した状態でテストしました。

    Excel2000/2007/2010/2013で試したところ、2007でのみ発生しました。

    原因究明のため、

    Debug.Print Range("A2").SpecialCells(xlCellTypeVisible).Address

    を試したところ、「$1:$2,$11:$1048576」が返りました。

    どうやら、SpecialCellsの対象セルが1つのセルであるとき、シート全体を対象範囲としてとらえてしまうようです。

    つまり、

    Debug.Print Cells.SpecialCells(xlCellTypeVisible).Address

    と同じ結果になります。

    前述の通り、2007でのみの発生なのでバグかと思います。(2016/2019は試していません。)

    このバグが原因で、Countを使うとオーバーフローします。

    CountLargeを使えばオーバーフローしませんが、そうして得られる値に価値がありません。

    回避策として、

        With Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
            Debug.Print Application.Intersect(.Areas(1), .SpecialCells(xlCellTypeVisible)).Count - 1
        End With

    などを考えます。

    ※フィルタで全て非表示のとき、Cells(Rows.Count, 1).End(xlUp)が「A1」になるので、「A2」を範囲開始位置にするのではなく、「A1」からとして、.Count からマイナス1するのがよろしいかと思います。



    • 編集済み minmin312 2019年6月27日 0:33 コード訂正
    2019年6月27日 0:15
  • infadeさん

    ご教示有難う御座います。

    教えて頂いたコードを試してみましたところ、選択されたセル範囲の末尾列合計が出てしまうようです。

    ですが、応用すれば行数を出せるかとも思いますので、他の方法を試してみたいと思います。

    2019年6月27日 1:47
  • KokemomoYamamomoさん、こんにちは。

    ご回答有難う御座います。

    私も上記URLにあるマクロを参考に作成してみました。

    やはり、2行目だけ抽出した際に、件数を取得するとオーバーフローになるというのはバグですかね。

    ご教示頂いたコマンドで実行してみましたところ、オーバーフローにはならなくなったのですが、

    取得する件数が想定のものと違った値を取得されてしまうようでした。

    (タイトル行も含めた行数を取得したり、4個見つかりましたとExcel画面上では出ていますが、実行結果が3件であったり。)

    正確に値を取得する方法や退避するやり方を検討してみます。

    2019年6月27日 2:39
  • infadeさん

    ご教示有難う御座います。

    教えて頂いたコードを試してみましたところ、選択されたセル範囲の末尾列合計が出てしまうようです。

    ですが、応用すれば行数を出せるかとも思いますので、他の方法を試してみたいと思います。

    すみません、先述のコードはちょっと間違えています。

        ' Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
        ' ↓ に変更する
        Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1, 1)
    

    ただ、ほかの方の回答の結果から考えてこれでうまくいくかどうかは
    わかりませんが。

    2019年6月27日 2:55
  • minmin312さん

    ご連絡有難う御座います。

    2007からのバグなんですね。

    ご教示頂いたコマンドでオーバーフローせず、フィルターで抽出した件数を正しく取得できました。

    このような書き方があるのですね。参考になりました。

    2019年6月27日 5:05
  • infadeさん

    有難う御座います、正しく行数を取得できました。

    頂いたコマンドを参考にさせて頂きます。

    2019年6月27日 5:14
  • kato_hiroさん、こんにちは。

    私もバグだと思います。

    それから、数がおかしい点、どうも .Rows.Count で行数が取得できると安易に考えたのが間違いのようです。十分な検証をしないまま返信をしてしまって、申し訳ありません。

    ほかの方の回答も参考に、次のような修正案を考えてみましたがどうでしょうか。
    Range(Cells(1, 1), Cells(Range("A1").CurrentRegion.Rows.Count, 1)).SpecialCells(xlCellTypeVisible).Count - 1

    この場合、オートフィルターの結果がゼロの場合は、0、1以上の場合はその数が返ります。ご参考まで。

    ※ ゼロの場合Range("A1").SpecialCells(xlCellTypeVisible).Count が実行されますが、Range("A1")はタイトル行のセルなので、オーバーフローにはならないのだろうと思われます。
    2019年6月27日 6:09
  • KokemomoYamamomoさん

    ご返信有難う御座います。

    ご教示頂いたコマンドで実行したところ、オーバーフローせず正常な件数を取得できました。

    参考にさせて頂きます。

    2019年6月27日 7:25
  • こっちの方がシンプルかも・・

    Debug.Print WorksheetFunction.Subtotal(103, Range("A1").CurrentRegion) - 1 '103=COUNTA

    2019年6月27日 9:35
  • minmin312さん

    有難う御座います。Subtotal関数を使うやり方ですね。

    詳細に分析頂き、色々とアドバイス頂きまして有難う御座います。

    ご教示頂いたコマンドだと、データ範囲全体の個数を求められてしまうようですので、以下のようにすれば件数を抽出できそうです。
    WorksheetFunction.Subtotal(103, Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))) - 1 '103=COUNTA
    • 編集済み kato_hiro 2019年6月28日 0:30
    2019年6月27日 23:37
  • すみません。1列のみのデータでテストしていたので考察が抜けていました。

    複数列あっても、ご提示のコードなら大丈夫そうですね^^;

    2019年6月28日 3:47
  • minmin312さん

    いえ、アドバイス頂きまして有難う御座います。

    助かります。

    2019年6月28日 4:07
  • こっちの方がシンプルかも・・

    Debug.Print WorksheetFunction.Subtotal(103, Range("A1").CurrentRegion) - 1 '103=COUNTA

    ちょっと試したり調べていたらどうやら Range.CurrentRegion は保護されている
    ワークシートに対して使うとエラーになるようなのでそういう要求があるときは
    事前にワークシートの保護を解除する処理も入れないといけないなど面倒になるので
    使えないかもしれないですね。
    (対象のワークシートを保護することがない場合は使えそうですけど)

    minmin312さん

    有難う御座います。Subtotal関数を使うやり方ですね。

    詳細に分析頂き、色々とアドバイス頂きまして有難う御座います。

    ご教示頂いたコマンドだと、データ範囲全体の個数を求められてしまうようですので、以下のようにすれば件数を抽出できそうです。
    WorksheetFunction.Subtotal(103, Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))) - 1 '103=COUNTA

    Range.CurrentRegion も Range.End(xlUp) を使った方法も実際にはデータの
    入力されている範囲などによって完全に正確な結果が得られるとは限らないのが
    ネックですね。

    あと、ワークシートが保護されていない状態であればこの場合は
    Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)) ではなく、
    Range("A1").CurrentRegion.Resize(, 1) としたほうが短くていいかも
    しれません。

    ちなみに実際に使うときは対象がアクティブでないことも考慮してワークブックと
    ワークシートも明示したほうがいいでしょう。

    2019年6月28日 6:10
  • infadeさん

    ご教示有難う御座います。

    CurrentRegionはシートが保護されているとエラーになるんですね。

    Range.CurrentRegion も Range.End(xlUp)も正確な結果が得られないことがあるんですか、それはネックですね。

    2019年6月28日 6:56
  • infadeさん、kato_hiroさん、こんにちは。

    前提条件として、確認したいのですが、保護されているシートでは、オートフィルタは使えないのでは?
    最初の質問で「VBAで、オートフィルタを設定しています。」と書いていらっしゃるので、保護は解除された状態での話なのではないでしょうか。

    次に、オートフィルターの設定範囲とRange.CurrentRegion で得られる範囲、及び Range.End(xlUp)で得られる範囲が一致しているのかどうかは、大きな問題で、気になっていたところです。一応、違いは御存じであろうという前提で返事をしていましたが、違いをご存知の上で オートフィルターの設定範囲としてRange.End(xlUp)でVBAを組んでいたのであれば、そのままRange.End(xlUP)で返事をすべきで、Range.CurrentRegion で返事をしたのはまずかったかもしれませんね。(オートフィルターの設定範囲内に空白の1行があると、違ってしまうので。)

    いずれにしても、ご両人のおっしゃる通りで、前提となる環境、条件を踏まえてVBAを構築する必要があることは、言うまでもありません。

    今回のご質問はオーバーフローする原因と対策についてのご質問でしたので、ひとまず、オーバーフローする原因は分析されましたので、後の対応策についても、いろいろ方策がご教示いただけたので、それらの方策から、kato_hiroさんが適切なものを選択してVBAを構築していただければよろしいのではないかと思います。小生も勉強になりました。ご健闘を祈ります。

    2019年6月28日 7:59
  • 保護されていてもオートフィルタは使えますよ。(AllowFiltering)

    それよりも、よくよく考えると空白文字列でフィルタを掛けた場合、Subtotalは使えませんね。

    Resize(,1)も、(対象列=CurrentRegionの左端列)なら問題ないかもですが・・・

    いろいろありますがケースバイケースで使い分けしてもらえればと思います。

    2019年6月28日 14:03
  • minmin312さん、おはようございます。

    ご指摘ありがとうございます。ちょっと横道にそれてしまいますが、念のため。

    おっしゃる通り、保護されているシートでもAllowFilteringで保護する前に設定されたオートフィルタをユーザーが使うようにすることはできるのですね。勉強になりました。しかし、保護されているシートにVBAで新たにオートフィルタを設定することは、AllowFilteringを使っても無理であると思いますが、違うでしょうか。

    kato_hiroさんのご質問では「VBAで、オートフィルタを設定しています。」と記されておりましたので、その趣旨は、VBAで新たにオートフィルタを設定して処理をする(実行して数をカウントする)、という事ではないかと考えた次第です。ご教示いただければ嬉しく存じます。

    (参照)保護するAllowFIlteringプロパティ(Excel)

    2019年6月28日 23:08
  • KokemomoYamamomoさん、minmin312さん

    色々とご教示頂き、有難う御座います。
    私が実行した環境では、特にシートが保護されている状態でなくオートフィルタを設定しております。

    工夫すれば、色々と構文を変えられるものですね。
    勉強になりました。
    2019年6月29日 3:25
  • KokemomoYamamomoさん

    > 保護されているシートにVBAで新たにオートフィルタを設定することは、

    > AllowFilteringを使っても無理

    その思考に沿うなら、VBAからシートの保護を解除してフィルタを設定すればいいだけでは?

    もしくは、シート保護の際に UserInterfaceOnly:=True としておくとか。

    2019年6月29日 9:27