Solved: Need faster code to lock and unlock the activesheet used range.

[frank_m]

The code shown below Runs in about 3/4 of a second on 20,000 rows, 30 columns of data, using my Vista PC with 3 gigs of ram.

But, when using an 8 year old Win XP pc, with only 500 mgs of ram -- an Intel 1 gighrtz processor(HyperThreading) they run in about half of that, or even slower than that... approx 1.5 to 2 seconds each.

Hope one of you guys can show me how to at least double the speed.
(Do you think using Kenneth Hobs speed on routine would help much ?)
[vba]
Sub UnLock_Range()
Dim wks As Worksheet, rng As Range, LastRow As Long
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row

Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 30))

.Unprotect

End With

With rng
.Locked = False
.FormulaHidden = False
End With

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True

End Sub
[/vba]
[vba]
Sub Lock_Range()
Dim wks As Worksheet, rng As Range, LastRow As Long
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row

Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 30))

.Unprotect

End With

With rng
.Locked = True
.FormulaHidden = False
End With

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True

End Sub
[/vba]

[Aflatoon]

You should be referring to wks rather than activesheet at the end of each routine. That may be slightly faster.

[frank_m]

Thanks for the feed back Aflatoon

Do you mean change to like this ?
[vba]
Sub Lock_Range()
Dim wks As Worksheet, rng As Range, LastRow As Long
Set wks = ActiveSheet
With wks
LastRow = .Range("G" & Rows.Count).End(xlUp).Row

Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 30))

.Unprotect

With rng
.Locked = True
.FormulaHidden = False
End With

.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True

End With

End Sub
[/vba]
That change did not speed it up at all unfortunately

[Aflatoon]

Yes, that is what I meant.
If you have protected the sheets using UserInterfaceOnly:=True previously, then you should not need to unprotect and reprotect the sheets in order to lock the cells. That may speed things up too. Or you may simply have to accept that an underpowered machine is never going to work quickly.

[frank_m]

If you have protected the sheets using UserInterfaceOnly:=True previously, then you should not need to unprotect and reprotect the sheets in order to lock the cells. That may speed things up too.

eureka -- Thanks Aflatoon
That did it. - That cut the time from 2 1/2 seconds average, down to one second average.

Problem Solved