frank_m
11-14-2011, 11:53 PM
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 ?)
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
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
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 ?)
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
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