Office TANAKA - ExcelのAI活用「[直して]実行速度が遅いマクロ(1)」

【2025年9月】

AIを使えば、エラーになるマクロを修正してもらえます。嬉しいですね。でも、多くのみなさんが望んでいることは、もうひとつありますよね。そう、マクロの実行速度です。VBAの基礎を学んでいない人が、マクロ記録とネットのコードを頼りにして、見よう見まねで作ったマクロはひどいです。とても"マクロ"とは呼べないような代物がほとんど。そんなもの、遅いに決まってるじゃないですか。技術がないから遅いんじゃありませんよ、無駄なことばかりやっているから遅いんです。無駄を省くだけで、マクロは普通の速度になります。さて今回は、処理に時間のかかるマクロを、AIのアドバイスで速くしてもらいましょう。

そもそも私、遅いマクロが書けません。どうしても、無駄のない高速なコードになってしまいますｗなので、がんばって"遅い状況"を作ってみました。

上図のリストは、データが10,000行あります。A列からC列までは値を入力してあります。D列はTEXT関数で曜日を取得し、E列はLEFT関数で左1文字を取得し、F列は数値に乱数を掛け算して整数化(=INT(C2*RAND()))しています。偶数行に色が塗られているのは条件付き書式です。=ISEVEN(ROW()) を条件に指定しました。さて、B列ですが、10,000件の中に"田中"が1,000件ランダムに入っています。このリストで「B列が"田中"である行を削除する」マクロを実行して、所要時間を計測します。まず実行したのは次のコードです。

Sub Macro1()
    Dim i As Long, lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 2 Step -1
        If Cells(i, 2) = "田中" Then
            Cells(i, 2).EntireRow.Delete
        End If
    Next i
End Sub

この手の"行削除"は、リストを下から見ていくのがセオリーです。1行削除されるたびに、3列の数式が再計算され、条件付き書式が更新されます。さて、まずは普通に実行したときの時間です。

26.250秒 (10回平均)

このコードをAIに送って、速くするための提案をしてもらいましょう。ちなみにですけど、提案は分かっています。まず「画面を止める」そして「再計算を手動にする」でしょう。

Sub Macro1()
　　Dim i As Long, lastRow As Long
　　lastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　For i = lastRow To 2 Step -1
　　　　If Cells(i, 2) = "田中" Then
　　　　　Cells(i, 2).EntireRow.Delete
　　　　End If
　　Next i
End Sub
このマクロの実行速度を速くするには？

実行速度を速くするには、以下の2点を改善すると効果的です。

画面更新・自動計算・イベントを一時停止する
削除対象を一括で削除する（UnionでまとめてDelete）
例：
Sub Macro1_Fast()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim i As Long, lastRow As Long
    Dim delRange As Range

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Cells(i, 2).Value = "田中" Then
            If delRange Is Nothing Then
                Set delRange = Rows(i)
            Else
                Set delRange = Union(delRange, Rows(i))
            End If
        End If
    Next i

    If Not delRange Is Nothing Then delRange.Delete

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub
この方法で大幅に高速化できます。

ほぉ、画面抑止と再計算だけでなく、Unionも提案してきましたか。さすがです。1,000回行削除するのではなく、Unionを使って1回の行削除にすれば、間違いなく高速化できるでしょうね。今回も一瞬だけ考えましたけど、けっこう面倒くさいんですよね、Unionって。初回のSetだけ処理を分けなくちゃいけないし。なので、別の手を使って"1回の削除"は試しています。詳しくは後述します。

提案されたコードを、さっそく実行してみたいのですが、その前に。実は「画面抑止」と「再計算の手動」は、提案されると思って、すでに試しています。

Sub Macro1()
    Dim i As Long, lastRow As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 2 Step -1
        If Cells(i, 2) = "田中" Then
            Cells(i, 2).EntireRow.Delete
        End If
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

それぞれの時間も計測してあります。

26.250秒 (10回平均) 1行ずつ削除
25.922秒 (10回平均) 画面更新を抑止
10.378秒 (10回平均) 画面更新を抑止＋再計算を手動

上記の結果を、よく見てください。ScreenUpdating = False で画面更新を抑止したって、今回のケースでは、それほど大きな効果は得られていませんよね。なんか、「とにかく画面を止めれば速くなる」って、理屈も分からず言っている人が多いですけど、違うんですよ。「画面更新の抑止＝マクロの高速化」じゃありません。速くなるときもあるだけです。やっぱね、今回はRAND関数ですよ。削除のたびに、約9,000個から10,000個のRAND関数が再計算されるのですから。さて、提案されたコードで速度を測ってみます。

26.250秒 (10回平均) 1行ずつ削除
25.922秒 (10回平均) 画面更新を抑止
10.378秒 (10回平均) 画面更新を抑止＋再計算を手動
4.916秒 (10回平均) 画面更新を抑止＋再計算を手動＋Union (←AIの提案)

いやぁ～ホントに、AIのマクロ(プログラミング)に関する提案は優秀です。AIは、PythonやJavaなど、多くのプログラミング言語に関して学習しているでしょうけど、今回のように「セル削除の回数を減らせば高速化できる」というのは、Excel特有の話です。そこを、ちゃんと分かっているのですから、お見事ですね。

さて、削除の回数を減らせば高速化できる、ってのは、やる前から分かってました。ちょっとだけUnionも考えましたけど、初回の条件分岐が面倒くさかったので、別の方法を使いました。オートフィルタです。B列を「"田中"と等しい」のフィルタをかけて、その結果を処理すれば、削除は1回で済みます。そのコードが下記です。

Sub Macro2()
    With Range("A1")
        .AutoFilter 2, "田中"
        .CurrentRegion.Offset(1, 0).EntireRow.Delete
        .AutoFilter
    End With
End Sub

これも、時間を計測してありますので、ご覧ください。

26.250秒 (10回平均) 1行ずつ削除
25.922秒 (10回平均) 画面更新を抑止
10.378秒 (10回平均) 画面更新を抑止＋再計算を手動
4.916秒 (10回平均) 画面更新を抑止＋再計算を手動＋Union (←AIの提案)
3.975秒 (10回平均) オートフィルタ
3.872秒 (10回平均) 画面更新を抑止
3.853秒 (10回平均) 画面更新を抑止＋再計算を手動

Unionに比べて、約1秒の差がありますけど、おそらくこれは「10,000回のFor Next」と「1,000回のオブジェクト変数へのSet」だと思います。オートフィルタは、シート上のデータを直接調べているのではありません。オートフィルタを設定すると、シート上のリストを、メモリ内で"データベース化"します。この仕組みを「インメモリデータベース」と呼びます。高速に動作するメモリ内で、検索などに特化したデータベースを作り、そこでフィルタを行っています。だからオートフィルタは、鬼のように速いです。今回みたく特定のデータを探して処理する場合、見つかるであろうデータが少ないときは、Cellsなどを使って1セルずつ処理しても大差ありませんが、対象のデータ件数が増えるほどオートフィルタが本領を発揮します。

まとめ

今回は、これだけにしておきます。あと「値貼り付けしてる遅いマクロ」なども考えていますが、それはまた別にコンテンツとして検証します。ちなみに、毎回10回ずつ時間を計測するのって、けっこう大変でしたｗだって、削除しちゃうんですから、毎回元の状態に戻さなくちゃならないし。ちなみに時間計測は、APIのGetTickCountを使いました。

「マクロが遅い」というのは、30年前から相談を受け続けています。コードを見せてもらうと、99%くらいは基礎が分かってなかったり、無駄な記述が多かったりが原因です。今回AIに相談しましたけど「画面抑止」と「再計算手動」は、提案されるまでもなく、知ってて当然というレベルです。さらに、そもそもAIが提示するコードには無駄がありません。どこを見たって「○○.Select」「Selection.××」なんて書いてないでしょ。無駄はないんです。だから、さらに高速化するためにUnionを使ったりしています。くれぐれもみなさんは、まずは"無駄をなくす"ことを心がけてください。何が無駄なのかは、VBAの基礎を学べば分かります。