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

Question

こんにちは

今、現在、とある条件にあった行を削除するマクロ作っているのですが、
インターネットを調べてみると後ろから探索して、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

mitarashi · Accepted Answer

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

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

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

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

以上、ご参考まで。

30246kiku · Answer

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

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

作業用に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 は不慣れなため、具体的説明は無理かも・・・・
（まずは、ヘルプを参照ください）

Siegrune · Answer

試された結果の通り、
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　と処理速度が違ったかも。
（ただ、少数の行の処理ではわからないと思います。）

mitarashi · Answer

徒然なるままにやってみました
いずれも
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とかしないで、同一サイズの配列に条件に合うデータを移し、丸ごと別シートに貼り付ける単純なコードです）ニーズに合うかどうかは分かりませんが、ご参考まで。

nao-y · Answer

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

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

#2です。

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

試された結果の通り、

徒然なるままにやってみました

後者は

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング