Speeding up a Data Validation macro

[Ryan]

Hi all,

I'm a novice at VBA and I'd appreciate some pointers on how this macro
I've written (well, recorded mostly) could be made to run faster. I'm
reading the defined range D7:CU213 on 84 sheets and looking for cells
which aren't protected (sub totals are protected)to apply some Data
Validation to. At the moment it's taking about 4 minutes per sheet,
which seems a tad slow to me for such a small range.

Anyways, here's the code, any help most appreciated.

-------------------------------------------
Sub DataVal()
' Loops through Range, tests for locked cells, applies Data Validation
to unlocked cells.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

For Each C In ActiveSheet.Range("D7:CU213").Cells
If C.Locked = False Then
C.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="0", Formula2:="99999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Input"
.InputMessage = ""
.ErrorMessage = "Input value must be numeric."
.ShowInput = False
.ShowError = True
End With
End If
Next

ActiveSheet.Protect
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Jim Cone]

Ryan,

This should help a little as it avoids cell selection,
eliminates the input message and declares the variable.

Regards,
Jim Cone
San Francisco, USA

'-----------------------
Sub DataVal()
' Loops through Range, tests for locked cells,
' applies Data Validation to unlocked cells.
Dim C As Excel.Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

For Each C In ActiveSheet.Range("D7:CU213").Cells
If C.Locked = False Then
With C.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="99999"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "Invalid Input"
.ErrorMessage = "Input value must be numeric."
.ShowInput = False
.ShowError = True
End With
End If
Next

ActiveSheet.Protect
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
'--------------------------------


Hi all,
I'm a novice at VBA and I'd appreciate some pointers on how this macro
I've written (well, recorded mostly) could be made to run faster. I'm
reading the defined range D7:CU213 on 84 sheets and looking for cells
which aren't protected (sub totals are protected)to apply some Data
Validation to. At the moment it's taking about 4 minutes per sheet,
which seems a tad slow to me for such a small range.
Anyways, here's the code, any help most appreciated.
-------------------------------------------
Sub DataVal()
' Loops through Range, tests for locked cells, applies Data Validation
to unlocked cells.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

For Each C In ActiveSheet.Range("D7:CU213").Cells
If C.Locked = False Then
C.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="0", Formula2:="99999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Input"
.InputMessage = ""
.ErrorMessage = "Input value must be numeric."
.ShowInput = False
.ShowError = True
End With
End If
Next

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

 

[Ryan]

Ryan,

This should help a little as it avoids cell selection,
eliminates the input message and declares the variable.

Regards,
Jim Cone
San Francisco, USA

Thanks mate. Your method is 22% faster.



cheers