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

Question

フォーラムの皆さま：

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

これはこれで動くのですが時間がかかります。データベースの行列はだいたい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

Answer 1

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

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

ＶＢＡ・マクロを簡単に高速化する方法（その１）
http://pcfunabashi.com/pcf-salon-VBAkousokuka1.html

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

---

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

Answer 2

Rangeオブジェクトは複数の範囲を表すことができます。
# ExcelでのCtrl+クリックのイメージ

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

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

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

Answer 3

Terapemiya様：

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

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

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

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

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

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

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

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

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

ＶＢＡ・マクロを簡単に高速化する方法（その１）
http://pcfunabashi.com/pcf-salon-VBAkousokuka1.html

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

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

---

---

LiLi803

Answer 4

返信と引用の違いがよくわからず、変な回答をしてしまっていたらお許しください。

---

LiLi803

Answer 5

Rangeオブジェクトは複数の範囲を表すことができます。
# ExcelでのCtrl+クリックのイメージ

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

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

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

佐祐理様

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

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

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

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

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

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

 

---

LiLi803

Answer 6

1行ずつ削除を行うと、どうしても時間がかかります。

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

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

---

甕星

Answer 7

1行ずつ削除を行うと、どうしても時間がかかります。

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

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

---

甕星

甕星様：

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

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

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

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

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

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

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

---

LiLi803