none
短いのに時間がかかる行削除のVBAコード: もう少しでも早く動くようにしたいのですが RRS feed

  • 質問

  • フォーラムの皆さま:

    下に貼りつけた通り、ある文字列と一致しないセルがある行を削除する単純なコードを書きました。

    これはこれで動くのですが時間がかかります。データベースの行列はだいたい3,000行ぐらいです。45秒ぐらいかかります。

    最終行から始めるから時間がかかるのか、何か速度を上げるためのコードが抜けているのかよくわかりません。

    もし、皆様がコードの実行速度を上げるために何かなさっているヒントがございましたら教えていただけますでしょうか?

    恐れ入ります。よろしくお願いいたします。

    りり

    Sub Delete_1_()

    Worksheets("Daily").Activate

    With ActiveSheet

    ‘列数を変数Cに格納する。

    C = 22

     列の最終行番号を変数lrに格納する。

    lr = Worksheets("Daily").Cells(Rows.Count, C).End(xlUp).Row

    Application.ScreenUpdating = False

    22の最終行から"AO1234-PATIENT PMT - PATIENT PAYMENT"Textと一致しないセルを含む行を削除する。

    For i = lr To 4 Step -1

    If Cells(i, C).Value <> " AO1234-PATIENT PMT - PATIENT PAYMENT " Then

    .Rows(i).Delete

    End If

    Next

    Application.ScreenUpdating = True

    End With

    End Sub


    LiLi803

    2017年1月31日 1:30

回答

すべての返信

  • 高速化にあたっては、画面描写の停止に加えて、自動計算の停止、イベントの無効化も行いますので、とりあえず、以下を参考に試してみて下さい。

    (参考)
    VBA マクロ高速化のために停止すべき3項目
    http://thom.hateblo.jp/entry/2015/08/31/063500

    VBA・マクロを簡単に高速化する方法(その1)
    http://pcfunabashi.com/pcf-salon-VBAkousokuka1.html

    VBA・マクロを簡単に高速化する方法その2
    http://pcfunabashi.com/pcf-salon-VBAkousokuka2.html


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年1月31日 1:45
    モデレータ
  • Rangeオブジェクトは複数の範囲を表すことができます。
    # ExcelでのCtrl+クリックのイメージ

    Application.Unionメソッドなどで削除対象のセルを1つのRangeオブジェクトに収めておき、最後に一度だけRange.Deleteを実行します。

    そうすることで、ワークシートに対する変更を一度だけに抑えることができるため動作が効率化されます。その場合、描画の停止などの措置も必要なくなります。

    ちなみにWorkSheet.UsedRangeプロパティを使用すると使用されているセル範囲を知ることができます。
    # Excelでスクロールバーが表示されている範囲。一旦、一番下まで行く必要がなくなります。

    2017年1月31日 2:16
  • Terapemiya様:

    アドバイスありがとうございました。

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

    で、コードをはさんでみました。

    若干早く削除されるようになりましたが、やはり待ち時間は体感的に長いな~・・・という感じでした。 カーソルがぐるぐる長く回ってます。

    VBA・マクロを簡単い高速化する方法2の配列ですが、まだ配列のことをよく勉強していないので配列の基本的なコードを一つしか書いたことがありません。 いや…よく勉強していないと言うのは事実ではないかもしれません。 もうずいぶん時間をかけて実用本などを読んでいるのですが、どうしてもすぱっと理解できません。 お恥ずかしい限りです。 これからもずっと勉強を続けますが、高速化する方法2の配列の方法をコードにする力はまだないと思います。でも配列を使うと高速化に貢献する・・・ということを覚えて、そのことも頭に入れながら配列の勉強を続けようと思います。

    ありがとうございました。

    >>高速化にあたっては、画面描写の停止に加えて、自動計算の停止、イベントの無効化も行いますので、とりあえず、以下を参考に試してみて下さい。

    (参考)
    VBA マクロ高速化のために停止すべき3項目
    http://thom.hateblo.jp/entry/2015/08/31/063500

    VBA・マクロを簡単に高速化する方法(その1)
    http://pcfunabashi.com/pcf-salon-VBAkousokuka1.html

    VBA・マクロを簡単に高速化する方法その2
    http://pcfunabashi.com/pcf-salon-VBAkousokuka2.html

    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/





    LiLi803

    2017年2月1日 1:52
  • 返信と引用の違いがよくわからず、変な回答をしてしまっていたらお許しください。

    LiLi803

    2017年2月1日 1:53
  • Rangeオブジェクトは複数の範囲を表すことができます。
    # ExcelでのCtrl+クリックのイメージ

    Application.Unionメソッドなどで削除対象のセルを1つのRangeオブジェクトに収めておき、最後に一度だけRange.Deleteを実行します。

    そうすることで、ワークシートに対する変更を一度だけに抑えることができるため動作が効率化されます。その場合、描画の停止などの措置も必要なくなります。

    ちなみにWorkSheet.UsedRangeプロパティを使用すると使用されているセル範囲を知ることができます。
    # Excelでスクロールバーが表示されている範囲。一旦、一番下まで行く必要がなくなります。

    佐祐理様

    アドバイスありがとうございました。

    やはり私の力不足でコード寄りに考えることができないのですが、イメージとしては対象列の対象データをSelectし、何らかの方法でAO1234-PATIENT PMT - PATIENT PAYMENTという文字列が入っているセルのみをSelectし直し、一気にDelete Entire Rowを実行する・・・ということになるのでしょうか?

    そうするとIF文や繰り返しは一切使わない・・・ということになるのでしょうか?

    Application.Unionメソッド も WorkSheet.UsedRangeプロパティ も自分で使ったことがないのでお恥ずかしいのですがやはりコードを組み立てることはできません。

    このセルの内容によって行を削除するという業務は仕事の中で嫌と言うほど出てきます。 いろいろ調べるとIFと繰り返しのサンプルコードが圧倒的に多いのですが、それ以外にも方法があるのですね。 もうちょっとメソッドとプロパティを理解しないとIF/繰り返しのコード以外書けません。 基盤のコードが書けるようになったら出直してきます。

    ありがとうございました。

     


    LiLi803

    2017年2月1日 2:09
  • 1行ずつ削除を行うと、どうしても時間がかかります。

    仕様上許されるなら、列Cでソートを掛けた後、Rows("10:15")の様に一致する範囲を指定して、一括で削除すると早いです。

    その後でもう一度ソートを掛けて、元の並びに戻します。場合によっては元の並びを保持するために、列を追加して並び順を持つといったことが必要になるかもしれません。


    甕星

    2017年2月4日 2:19
  • 1行ずつ削除を行うと、どうしても時間がかかります。

    仕様上許されるなら、列Cでソートを掛けた後、Rows("10:15")の様に一致する範囲を指定して、一括で削除すると早いです。

    その後でもう一度ソートを掛けて、元の並びに戻します。場合によっては元の並びを保持するために、列を追加して並び順を持つといったことが必要になるかもしれません。


    甕星

    甕星様:

    アドバイスをありがとうございました。

    ソートをかける方法は考えておりませんでした。ソートをかけた後に残したい文字列が入っているセル以外の範囲を指定する必要がありますね。 残したい文字列が列Cの真ん中あたりに来ますので、その上の範囲(削除したい)その下の範囲(削除したい)の2つを指定する必要がありそうです。

    ただ、文字列の値によってセルを選択するというコードがパッと浮かびませんので調べてみます。

    ソートが使えるならオートフィルタを使った削除と言うこともできるのか調べてみます。

    元の並びはそんなに大切ではありません。 残したい文字列が入った行以外を全部削除したら、その後分析のために順番をいろいろ変えるのでその際希望の順に並び替えることができます。

    削除のコードは時間がかかるんですね。そのこと自体あまりよく知りませんでした。

    ありがとうございました。



    LiLi803

    2017年2月6日 1:20