VBAで複数検索、AdvancedFilter

質問

AdvancedFilterを使って、検索を行っているのですが、応答なしと固まってしまったり、動作が重くなったりするのですが、原因わかりますでしょうか？

3000行くらいなのですが、ユーザーフォームを立ち上げて、検索、結果を見て、また検索をしようとすると固まったりして動かなくなって強制終了になる場合があります。

軽くなる方法はありますか？

Private Sub CommandButton1_Click()
Worksheets("商品マスタ").Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
ActiveWindow.ScrollRow = 3
Range("S1:AA4").ClearContents
Range("S1:AA4").NumberFormatLocal = "@"
Range("T2:U2").Value = Range("B2:C2").Value
Range("V2:X2").Value = Range("D2").Value
Range("Y2:Z2").Value = Range("E2:F2").Value
Range("AA2").Value = Range("G2").Value
If Me.TextBox1.Value <> "" Then ' コード
Range("U3").Value = "*" & Me.TextBox1.Value
End If
If Me.TextBox2.Value <> "" Then ' メーカー
Range("V3").Value = "*" & Me.TextBox2.Value & "*"
End If
If Me.TextBox3.Value <> "" Then ' シリーズ
Range("W3").Value = "*" & Me.TextBox3.Value & "*"
End If
If Me.TextBox4.Value <> "" Then ' サイズ
Range("W3").Value = "*" & Me.TextBox4.Value & "*"
End If
If Me.TextBox5.Value <> "" Then ' 入荷日
Range("T3").Value = Me.TextBox5.Value
End If
If Me.TextBox9.Value <> "" Then ' 仕入れ先
Range("Z3").Value = Me.TextBox9.Value
End If
If Me.TextBox12.Value <> "" Then ' 単体価格
Range("AA3").Value = Me.TextBox12.Value
End If
If Me.TextBox6.Value <> "" Then ' 在庫数
Range("Y3").Value = Me.TextBox6.Value
End If
If Cells(3, Columns.Count).End(xlToLeft).Column > 19 Then
Range("A2:G" & Rows.Count).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("S2").CurrentRegion, Unique:=False
End If
Range("S1:AA4").ClearContents
ActiveWindow.ScrollColumn = 4
Range("A2").Activate
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

回答No.5

WindFaller

こんにちは。

>エクセルを開いたあと、インターネットやアウトルック等を起動したりしているので、それも良くないのでしょうか。

そんなことはありません。あくまでも、ThisWorkbookやAuto_Open で、インターネットアクセスなどは、重いファイルの問題かもしれませんが、しないほうがよいようですね。ファイルが完全に開くまで、安定するまでは、アクセスしないということだけです。

Webクエリって、名実ともに組み込みマクロで、確かタイマー機能まで備えているわけですよね。だから、設定にもよりますが、自動的に動くわけですね。ファイルが安定していない状態だったら、やっぱりまずいのかなって思うのです。それで、私は、マクロを使うなら、マクロの上のマクロになってしまうので、QueryTableを削除してしまうわけです。

人によっては違う意見もあるとは思うのですが、10年以上もマクロを使っていると、いろんな場面に遭遇しますので、あれこれと自分なりの結論を作ってしまっているかもしれません。

なお、私は、ここの掲示板を、ExcelのVBAで、IEのオートメーション（オブジェクト）で、新規の質問リストを取ったり、自分の発言した管理を取ったりしていますが、書き込む際は、IE でアクセスしっぱなしでのこともあります。Excel2003時代から、現在のExcel2010まで、致命的エラーは、ほとんどありません。

補足

Webクエリってそこまで出来るんですか！

新規の質問リストをとったりってことは、一覧リストから入って、中身も読み取れるのですか？？

私はマーケティングをしており、WEBクエリで出来るのかなと思いながら、手作業でやっておりますが、

マーケティング価格調査は中身を見て、税抜き価格なのか税込価格なのか、本当の値段を調査するには詳細ページを見ていかないとでないので、クリックして見ていってるのですが、果てしない作業で大変なのですが、

WEBクエリを使えば、価格調査も出来ると言うことなんですか？

知識が豊富で素晴らしいですね。本当に勉強になります。

補足ばかりですいません。。

回答No.4

WindFaller

こんばんは。

>他のシートで別の作業WEBクエリを使って作業をしたあとに、するとマクロが反応しないときはあるので、ごちゃごちゃしすぎてしまってるのかもしれません

WEBクエリは、単独のシートで行っていますか？
わたし流ですが、そこに何かを書き込むようなことをすると、調子が悪くなるようで、そこから必要なデータは、もう一度、数式などで別のシートに取り出すようにしています。

たぶん、WEBクエリは、テキストでとれているはずですが、余計なデータが紛れ込んでいる可能性があります。いじらなければ、問題は起こさないのですが、そのシートで、あれこれ作業すると、よくないのかもしれません。私の場合は、WEBクエリでデータを取得した後、その本体のオブジェクトを最後に削除することが多いです。

例：ActiveSheet.QueryTables(1).Delete

それと、私のメモをみると、WEBクエリの項目の中に、「Excelの起動時直後に、他のオートメーション・オブジェクト（IEなど）を使用すると安定が悪く、エラー発生することがあるようです。」となっています。これだけでは意味不明かもしれませんが、OnTime メソッドとの組み合わせになどについて、自動実行させる場合の注意事項です。

補足

なるほど。単独シートで挿入しておりますが、そこの必要箇所をコピーしてメインのデータが入っている商品マスタシートへコピーして、条件付き書式を使って重複データがあるか、探したりしてます。

その後、検索を行うと反応しなくなったりします。

あと、エクセルを開いたあと、インターネットやアウトルック等を起動したりしているので、それも良くないのでしょうか。

色々教えて頂いて勉強になります。ありがとうございます。

回答No.3

WindFaller

#2の回答者です。

>ここを変えれば良いということでしょうか？？

Range("S1:AA4").NumberFormatLocal = "@"
を、
'Range("S1:AA4").NumberFormatLocal = "@"

基本的には不要だと思っていますから、削除してみるか、コメントブロックをつけて、動かないようにしてみてください。失礼ですが、この程度の話にも質問されてしまうと、こちらからは、何も言えないです。

なお、
Range("S1:AA4").ClearContents
で止まるということですが、そこに問題があるとすれば、一度、その範囲を、書式など含めて完全な消去をしてみる手もありますね。

ただ、後は、ブック自体を移し替えてみることでしょうか？

お礼

ありがとうございます！！

ブック自体を作り直したら、今のところ、固まることがなくなりました。

ただ、他のシートで別の作業WEBクエリを使って作業をしたあとに、するとマクロが反応しないときはあるので、ごちゃごちゃしすぎてしまってるのかもしれません、、

回答No.2

WindFaller

こんにちは。

まず、ExcelのバージョンとOSのバージョン（32か64 bitを含め）ていくつですか？
全体の感じからして、なんとなくUserFormの問題があるような気がします。
もともと、UserFormというものは、ローカル・オブジェクトでありながら、ThisWorkbookから直接のぶら下がりのオブジェクトになっています。そのせいか、UserFormを搭載したExcelは、使い方によって、フリーズになったり、致命的エラーを発したりして、とてもデリケートな状態になることがあります。

今のようなコードは、それぞれの親オブジェクトがあやふやで、アブナイなっていう印象があります。ただ、それ自体が問題が発生することは結び付けられません。

問題のひとつは、
Range("S1:AA4").NumberFormatLocal = "@"
ご存知でないかもしれませんが、これは、まずいです。
関連のセル自体、不活性化させてしまいますから、
必要な部分だけ、マクロでは「'」と。プレフィックス文字を入れて使うようにしたほうがよいです。

例：
Range("A3").Value = "'12/12"

もうひとつは、関係ないかもしれませんが、
With ActiveSheet '（または、Worksheets("商品マスタ")）
　.Range(～） ' コンマ（.）が文頭に付く
End With
というスタイルにしてみればよいと思います。

ただ、#1の補足のpoint2 の
>これに変えたら、マクロが反応しなかったので、元に戻しました
とは、構造的に考えられませんから、何かブック全体に問題が発生している可能性があります。a

補足

ありがとうございます。でもすいません。VBAに詳しくないため、どう変えれば良いかが分からないのですがRange("S1:AA4").NumberFormatLocal = "@"
ここを変えれば良いということでしょうか？？

回答No.1

keithin

【point1】
変更箇所：
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

この前に
application.calculation = xlcalculationmanual
を挿入し，再計算で時間が掛かっていないか確認します

マクロの最後で
application.calculation = xlcalculationautomatic
で自動に戻して下さい

【point2】
変更箇所：
Range("A2:G" & Rows.Count).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("S2").CurrentRegion, Unique:=False

とりあえず簡易に
Range("A2:G4000").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("S2:AA4"), Unique:=False

に変更し，改善するか確認します。
必要に応じて「最終行」をあなたの実際のエクセルシートに応じた方法で調べ（たとえばＡ列の一番下の行の行番号までなどのように）て，そこまでの範囲でadvancedfilterを作用させるような仕込みにしてみます。

【point3】
マクロの各所に
stop
を挿入し（判るならもちろんブレイクポイントを設定するのでも構いません），どこまでのステップはさくっと進み，具体的にどのマクロの一行でフリーズするのか焙り出します。

http://kabu-macro.com/word/ha-ho/breakpoint.html

お礼

Range("S1:AA4").ClearContents

ここにマーカーがついて、デバックを中断しますと出たので、ここの箇所が固まる原因と言うことなのでしょうか？

補足

【point1】
は入れてみました。

【point2】
変更箇所：
Range("A2:G" & Rows.Count).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("S2").CurrentRegion, Unique:=False

とりあえず簡易に
Range("A2:G4000").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("S2:AA4"), Unique:=False

これに変えたら、マクロが反応しなかったので、元に戻しました。

ブレイクポイントのサイトを見ましたが、どこでフリーズするのかいまいちわからないですが、全行選択したらいいのでしょうか？

固まるのは、検索後、シートの行をクリックしようとすると毎回固まってしまいます。