【ExcelVBA】【VBA高速化】For～Each文でのRange操作

Question

For~Each文でのRange操作で、以下のマクロを高速化したいです。（現状、かなり遅い）

マクロの概要

• 条件付き書式でグレーアウトされているセルをロック
• 数式が入力されているセルをロック

        Dim rng As Range
        With wsBSinput
            eRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            .Range(.Cells(pcDataRow, 9), .Cells(eRow, 52)).Locked = False

            For Each rng In .Range(.Cells(pcDataRow, 9), .Cells(eRow, 52))
                ' グレーアウトされているセルはロック
                If rng.MergeArea.DisplayFormat.Interior.ColorIndex = 15 Then
                    rng.MergeArea.Locked = True
                ' 自動計算セルはロック
                ElseIf rng.MergeArea(1, 1).HasFormula = True Then
                    rng.MergeArea.Locked = True
                End If
            Next rng
        End With

なお、このプロシージャを呼ぶ前に、以下のことはすでにしています。

• イベントの抑制
• 画面描画の停止
• 自動計算を手動

何かわかる方いたらご教授いただければ幸いです。
よろしくお願いいたします。

Answer 1

数式の部分はFor Eachの外でもいけるかと思いました。

.Range(.Cells(pcDataRow, 9), .Cells(eRow, 52)).SpecialCells(xlCellTypeFormulas, 23).Locked = True

Comments

[Tera-Tomi]

最初、SpecialCellsで数式部分をロックする方法もやったのですが、改善されず。
しかし、このようにFor Eachの外でもできるのですね。勉強になりました。ありがとうございます。

Answer 2

これでどうでしょうか。

Dim rng As Range
Dim sel As Range
With wsBSinput
    eRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    .Range(.Cells(pcDataRow, 9), .Cells(eRow, 52)).Locked = False

    Set sel = Range("A1") ' A1をダミーで登録
    For Each rng In .Range(.Cells(pcDataRow, 9), .Cells(eRow, 52))
        ' グレーアウトされているセルはロック
        If rng.MergeArea.DisplayFormat.Interior.ColorIndex = 15 Then
            Set sel = Union(sel, rng.MergeArea)
        ' 自動計算セルはロック
        ElseIf rng.MergeArea(1, 1).HasFormula = True Then
            Set sel = Union(sel, rng.MergeArea)
        End If
    Next rng
    sel.Locked = True ' 一括ロック
    Range("A1").Locked = False ' A1のロックを解除
End With

Comments

[Tera-Tomi]

このようにすれば一括でロックできるのですね。勉強になりました。
実際に、こちらのマクロで動かしてみたところ、劇的に早くなりました。
ありがとうございました！