Nate Oliver's blog

Good evening,

We'll keep tonight's entry short. If you want to blast through a multi-cell Range of cells worth of data, I have a few thoughts. Use a buffered array, limit your property toggles to that which is necessary, e.g., don't flicker the Protect Property of the Worksheet on each pass in the loop.

With that, here's the example:

Sub foo()
Dim myArr() As Variant
Dim i As Long, j As Long
Let Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=vbNullString
Let myArr = Range("A7:C2367").Value
    For i = LBound(myArr, 1) To UBound(myArr, 1)
        For j = LBound(myArr, 2) To UBound(myArr, 2)
            If LenB(myArr(i, j)) > 0 Then
                With Cells(i + 6, j)
                    .Locked = True
                    .FormulaHidden = False
                End With
            End If
        Next
    Next
ActiveSheet.Protect Password:=vbNullString
Application.ScreenUpdating = True
End Sub

This should have some pretty fast shoes on her. It's a 1-based Array, if you're wondering why I'm adding 6. With that, happy MLK Weekend!

Best,
Nate Oliver