Speeding Up VBA execution

waygar 

Please start a new discussion with that question. I cannot help you with it.

Hi Hans,
Is there a way, using VBA, to change the permissions for defined users such as they can, edit or not, the Allow Edit Ranges set in a Protected Sheet using VBA and not just change the permissions manually each time eg when a user (with limited editing permission) is changed from being just a user to an administrator (with full editing permission)?

waygar

That is correct - keeping the formatting would slow down the code immensely.

The choice is yours...

Works like a charm - I didnt use a tmp variable thinking the extra step would impose a delay.
However usage of arrays does not keep the formatting , correct?

waygar 

Try this version:

Sub Switch()
    Dim v As Variant
    Dim NRows3 As Long
    Dim I As Long
    Dim tmp1, tmp2, tmp4

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    NRows3 = 25330
    v = Range("E6:I" & NRows3).Value
    For I = 1 To UBound(v)
        tmp1 = v(I, 1)
        v(I, 1) = v(I, 3)
        v(I, 3) = tmp1
        tmp2 = v(I, 2)
        tmp4 = v(I, 4)
        v(I, 4) = v(I, 5)
        v(I, 2) = tmp4
        v(I, 5) = tmp2
    Next I
    Range("E6:I" & NRows3).Value = v

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Please test on a copy of the worksheet.

waygar 

Yes, but that is a single number - the code applies to row 25330, not to any other rows.

Or am I mistaken?

Sorry, I didn't look closely enough.

Stay tuned.