Unionによる処理速度向上効果について

Rangeオブジェクトへの処理はまとめて実行すると早い
⇒ Unionでまとめてから実行するとマクロが高速化する

ということが良く述べられています。

今回はそれが正しいかを検証していきましょう。

お忙しい方向けに先に結論を示しますと、

Union自体がそこそこ遅いメソッドであるため、

• Unionよりも速い「書式設定」などの処理はUnionを使った方が遅い
• Unionよりも遅い「列の削除」などの処理はUnionを使った方が早い

また、Union自体の性能(性質)として、

• 複数のセル範囲をまとめるのは時間がかかる
• 複数の行をまとめるのはそれほど時間がかからない

という性質を持っています。

興味がある方は、以下の検証結果を眺めてみてください。

使用するデータレイアウト

今回検証するデータはこんなレイアウトのシートです。

セルの書式設定の処理時間

まずははじめにセルの書式設定を行ってみましょう。

セルの背景色を、

• Unionでまとめてから着色する
• For文で1セルずつ着色する

で計測してみます。

ソースコード

Sub Unionでまとめてから着色する()

    Dim 開始時刻 As Double, 終了時刻 As Double
    
    開始時刻 = Timer
    
    Dim 着色セルリスト As Range: Set 着色セルリスト = Range("A2")
    Dim R As Long
    For R = 4 To 2000 Step 2
    
        Set 着色セルリスト = Union(着色セルリスト, Cells(R, 1))

    Next

    着色セルリスト.Interior.Color = RGB(255, 0, 0)

    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

End Sub

Sub For文で1セルずつ着色する()

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim R As Long
    For R = 2 To 2000 Step 2
    
        Cells(R, 1).Interior.Color = RGB(255, 0, 0)

    Next
    
    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

End Sub

計測結果

実行マクロ	処理時間
Unionでまとめて着色	1.7265625秒
For文で1セルずつ着色	0.046875秒

圧倒的にUnionを使わない方が早くなっていることがわかりますね。

この原因を探るため、以下の追加コードでUnionの計測時間を細かくとってみます。

Sub Unionでまとめてから着色する()

    Dim 開始時刻 As Double, 終了時刻 As Double
    
    開始時刻 = Timer
    
    Dim 着色セルリスト As Range: Set 着色セルリスト = Range("A2")
    Dim R As Long
    For R = 4 To 2000 Step 2
    
        Set 着色セルリスト = Union(着色セルリスト, Cells(R, 1))

    Next

    ' UnionでRangeを生成する部分までを一旦計測
    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"
    開始時刻 = Timer '

    着色セルリスト.Interior.Color = RGB(255, 0, 0)

    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

End Sub

実行マクロ	処理時間
Union実行部	1.703125秒
着色実行部	0.015625秒

この実行結果を見るとわかる通り、Rangeオブジェクトを1つにまとめることで、
確かに着色処理は0.045秒→0.015秒と3倍速になっています。

しかしながら、そのためにUnionで処理をまとめる部分が、
1.7秒と実に40倍もの時間を要していることが分かります。

このように、
1セルずつ処理するよりセル範囲ごとまとめて処理した方が早い
のですが、
Union自体が割と遅いメソッドで、そのメリットを帳消しにしてしまう
という性質があります。

確かに「色を塗ってフォントを変えて罫線を引いて」と処理が増えると、
まとめる効果は少しずつは増えるということもあります。

が、40倍となると流石に逆転することはありません。

また、Unionは元の領域数が増えるほど結合に時間がかかる性質を持ち、
例えば5000行とデータが2.5倍になっただけで、
処理時間は30秒弱と、10倍以上の時間を要するようになります。

書式の設定において、Unionによる高速化効果はないと覚えておいてください。

行の削除を行う場合の処理時間

続いて有名なコード「行の削除」を検証していきます。

行の削除は1行ずつやるとループカウンタの制御が難しいため、

• Unionでまとめてから一括削除する
• Step -1 を使用して下から上に向かって削除していく

この2種類の方法が良く挙げられますので、
このどちらが早いかを検証します。

まずは「みかん」を削除するコードを検証してみます。

20品目のデータのため、2000行中100行を削除する程度です。

ソースコード（削除行が少ない場合）

Sub For文で1行ずつ削除()

    'Application.ScreenUpdating = False ' ← これはONOFFしてみる

    Dim 最終行 As Long: 最終行 = WSデータ.UsedRange.Row + WSデータ.UsedRange.Rows.Count - 1

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim R As Long
    For R = 2000 To 2 Step -1

        If WSデータ.Cells(R, 7) = "みかん" Then
            WSデータ.Rows(R).Delete
        End If
        
    Next

    終了時刻 = Timer
    Debug.Print "Forで1行ずつ削除：" & (終了時刻 - 開始時刻) & "秒"

End Sub

Sub Unionで結合してから削除()

    'Application.ScreenUpdating = False ' ← これはONOFFしてみる

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim rows削除行 As Range
    Dim R As Long
    For R = 2 To 2000

        If WSデータ.Cells(R, 7) = "みかん" Then
            If rows削除行 Is Nothing Then
                Set rows削除行 = WSデータ.Rows(R)
            Else
                Set rows削除行 = Union(rows削除行, WSデータ.Rows(R))
            End If
        End If
        
    Next
    
    If Not rows削除行 Is Nothing Then
        rows削除行.Delete
    End If

    終了時刻 = Timer
    Debug.Print "Unionで結合してから削除：" & (終了時刻 - 開始時刻) & "秒"

End Sub

計測結果

実行マクロ	描画あり	描画なし
Unionでまとめて削除	0.046875秒	0.0390625秒
For文で1行ずつ削除	0.0625秒	0.0390625秒

この結果を見ると、削除する行が少ない場合は、
描画を停止すればほぼ同速で実行できていることが分かります。

続いて「=みかん」を削除していたこのコードを、
「<>みかん」を削除(ほぼ全行を削除)するコードに変えてみましょう。

この場合は2000行中1900行を削除するレベルの大規模削除になります。

ソースコード

Sub For文で1行ずつ削除()

    'Application.ScreenUpdating = False ' ← これはONOFFしてみる

    Dim 最終行 As Long: 最終行 = WSデータ.UsedRange.Row + WSデータ.UsedRange.Rows.Count - 1

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim R As Long
    For R = 2000 To 2 Step -1

        If WSデータ.Cells(R, 7) <> "みかん" Then
            WSデータ.Rows(R).Delete
        End If
        
    Next

    終了時刻 = Timer
    Debug.Print "Forで1行ずつ削除：" & (終了時刻 - 開始時刻) & "秒"

End Sub

Sub Unionで結合してから削除()

    'Application.ScreenUpdating = False ' ← これはONOFFしてみる

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim rows削除行 As Range
    Dim R As Long
    For R = 2 To 2000

        If WSデータ.Cells(R, 7) <> "みかん" Then
            If rows削除行 Is Nothing Then
                Set rows削除行 = WSデータ.Rows(R)
            Else
                Set rows削除行 = Union(rows削除行, WSデータ.Rows(R))
            End If
        End If
        
    Next
    
    If Not rows削除行 Is Nothing Then
        rows削除行.Delete
    End If

    終了時刻 = Timer
    Debug.Print "Unionで結合してから削除：" & (終了時刻 - 開始時刻) & "秒"

End Sub

実行マクロ	描画あり	描画なし
Unionでまとめて削除	0.0625秒	0.046875秒
For文で1行ずつ削除	4.1015625秒	2.4921875秒

残す行より削除する行の方が多い場合は、
Unionで一括削除する効果が大きく、かなり高速化できていますね。

また注目すべき点として、Unionの実行回数が増えているにもかかわらず、
1セルずつUnionした着色処理より1行ずつUnionした削除処理の方が、
Union自体にかかる時間も短くなっているのが分かります。

Unionは行をまとめるにはそこまで時間がかかりませんが、
セルをまとめるには時間がかかるというポイントも覚えておきましょう。

結論としては、

Union自体がそこそこ遅いメソッドであるため、

• Unionよりも速い「書式設定」などの処理はUnionを使った方が遅い
• Unionよりも遅い「列の削除」などの処理はUnionを使った方が早い

という結果となりました。

また、Unioinの性質として、

• 複数のセル範囲をまとめるのは時間がかかる
• 複数の行をまとめるのはそれほど時間がかからない

ということも分かりました。

「データの中間を行ごと削除する」より遅い処理はなかなかないので、
とりあえず、

• 「行の削除についてはUnionが有効」
• それ以外は普通にFor文を回した方が良い

と覚えておけばいいと思います。

複数のセル領域を扱うマクロの速度を気にするときは、
この結果を参考にしてください。