条件に合った行を削除するマクロについて

質問

こんにちは

今、現在、とある条件にあった行を削除するマクロ作っているのですが、
インターネットを調べてみると後ろから探索して、1行ずつ消していくのがいいと書いてありました。

まぁ、その理屈はわかるんですが、それなら
「Unionでセルの範囲を結合してから、最後に一度に消してしまった方が速いのでは」
(消す作業が1度だけで済むから)

と思い試してみたんですが、実際試したところ・・・

ものすごく遅かったです。
(ちなみに、1万件のデータで削除した行数は6000ほどでした）

何故Union結合だと遅いのでしょうか？

速いマクロを作成するには、やはり後ろから探索して、1行ずつ消していくしかないのでしょうか？

以下は試したマクロです。
(test が　unionで試したマクロ、test2が後ろから1行ずつ削除したマクロ)

Option Explicit

Public Sub test()

Dim r As Range
Dim r1 As Range

'Cells.Replace "-", " "

For Each r In Range("A2", Range("A65536").End(xlUp))

If r = r.Offset(1, 0) And r.Offset(0, 1) < r.Offset(1, 1) Then
If r1 Is Nothing Then
Set r1 = r
Else
Set r1 = Union(r1, r)
End If
End If
Next

r1.EntireRow.Delete
' r1.Select
End Sub

Public Sub test2()

Dim r As Range
Dim r1 As Range
Dim i As Integer
'Cells.Replace "-", " "

Application.ScreenUpdating = False

For i = Range("A65536").End(xlUp).Row To 1 Step -1

If Cells(i, 1) = Cells(i + 1, 1) And Cells(i, 2) < Cells(i + 1, 2) Then
Cells(i, 1).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True

End Sub

回答No.5

mitarashi · ベストアンサー

#2です。
行を削除するのはいずれにしても遅いと思うので、抽出する方法を追加で試してみました。

xl2010(32bit), WinXP SP3, 昔のCeleron 2.4GHzの環境です。
データは#2と同じで、1万行の内、半分程度を削除する条件です。

○下から、行削除　平均17秒程度
○セル範囲→配列→別配列に抽出→セル範囲に丸ごと代入　平均1.9秒程度
○フィルタオプションで別シート抽出　0.6秒程度

・フィルタオプションは意外に速く、見直しました。（抽出条件の複雑さにもよると思いますが）
・xl2010はxl2003までに比べて重たいイメージでしたが、行削除に何倍もかかるという訳ではありませんでした。

以上、ご参考まで。

お礼

回答遅くなりましたすみません。

なるほど、フィルタオプションですか。
エクセルの既存の機能を使った方が速いんですね。

他の方もいろいろ案を出してくださったのですが、実際に速度まで測って検討してくださったmitarashiさんをベストアンサーにさせていただきます

ありがとうございました

回答No.4

30246kiku

解決されていましたらスルーしてください。

速い遅いはわかりませんが、以下ではどうでしょうか

作業用にK列を使ってます。
都合悪ければ、With .Offset(, 10)　部分を変更してください。

Sub Sample()
　　On Error Resume Next
　　With Range("A2", Cells(Rows.Count, 1).End(xlUp))
　　　　With .Offset(, 10)
　　　　　　.Formula = "=IF(AND(A2=A3,B2<B3),1,"""")"
　　　　　　.Value = .Value
　　　　　　.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
　　　　　　.ClearContents
　　　　End With
　　End With
End Sub

試される時には、戻せる状態で行ってください。

なお、上記は以下を参考にしていました。

VBAのことを教えてください
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1486329244

EntireRow を Union する方法もあるようです。

※　Excel VBA は不慣れなため、具体的説明は無理かも・・・・
（まずは、ヘルプを参照ください）

お礼

回答ありがとうございます。
返答が遅くなりまして申し訳ありません。

教えていただいた方法試して見ます。

回答No.3

Siegrune

試された結果の通り、
Unionした結果に対して行を追加するのは遅かったと思うので、
複数の行の選択を１回でして、削除をすると別に遅くはないのでは？

Public Sub test3()
Dim r As Range
Dim r1 As Range
dim wkstr as String
wkstr = ""
For Each r In Range("A2", Range("A65536").End(xlUp))
If r = r.Offset(1, 0) And r.Offset(0, 1) < r.Offset(1, 1) Then
If wkstr = "" Then
wkstr = trim(cstr(r.row)) & ":" & trim(cstr(r.row))
Else
wkstr = wkstr & "," & trim(cstr(r.row)) & ":" & trim(cstr(r.row))
End If
End If
Next

range(wkstr).Delete
' r1.Select
End Sub
といった感じででも試してみられては。
（未検証）

・・・Cells(i, 1).EntireRow.Delete　と　rows(i).delete　と処理速度が違ったかも。
（ただ、少数の行の処理ではわからないと思います。）

お礼

回答ありがとうございます。
返答が遅くなりまして申し訳ありません。

教えていただいた方法試して見ます。

回答No.2

mitarashi

徒然なるままにやってみました
いずれも
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
を入れてあります。今回のケースでは後者は0.数秒くらいしか効きませんでしたが。

xl2000,WinXP SP3,初代Pentium4 3Gの環境です。
A,B,C列が数文字の文字列で、D列が3桁の乱数。乱数が500より大きい行を削除する事例です。
10000行に合わせました。

１．普通に下から行（行全体）を削除する　11秒台
２．条件に合うセル範囲を一旦Unionして丸ごと削除する　50数秒台
（範囲内だけ消しても、行全体を消しても大差無し）
と、ご指摘の通りUnionする方が数倍遅かったです。条件分岐等でRangeを操作する回数が増えるので、遅くなるのかもしれません。試しにUnionしたセルを丸ごと消すところをコメントアウトして実行すると40数秒かかりましたので、行を消すのにかかる時間は、１，２共に同じ位で、Unionする部分に時間がかかる分だけ遅くなると言えそうです。
ご自分のコードの要所にGetTickCountを入れて確認されれば良いと思います。

速くしたければ、一旦配列に収納して、上記の例では500未満のデータだけ別シートに書き出す方法をとれば数分の1～十分の一位の時間で処理可能でした。（redimとかしないで、同一サイズの配列に条件に合うデータを移し、丸ごと別シートに貼り付ける単純なコードです）ニーズに合うかどうかは分かりませんが、ご参考まで。

お礼

Unionはどうも、結合する数が膨大になっていくにつれて遅くなっていくような気がしました。

一概に早いマクロって理論道理にいかないものですね・・・
早いマクロ作るにはなかなか苦労します。
1万件もあるファイルが6ファイルもあるので、なるべく早いマクロを試してみようと思います。

配列のアイデアありがとうございます
作業シートを使うと早くなりそうですね

回答No.1

nao-y

後者は
Application.ScreenUpdating = False
を入れているから早いのでは？

補足

前者にApplication.ScreenUpdating = Falseを入れても変わりませんでした。
ちなみに前者Unionの方は2～3分かかるのに対し、後者は数秒しかかかりません。

また、union結合を使って、1行おきにセル範囲を結合していくマクロを作成した場合でも、同じくらい時間がかかる為、ボトルネックはUnion結合にあると考えます