VBA for data validation on massive input sheet

[EdwardAveyard]

Hello. I'm sure that many of you will know that Excel's data-validation tool from the ribbon is easily overridden by copying and pasting a cell from elsewhere. I'm aware that VBA can be used for validations but I don't know how to do it.

I found one macro online that seemed to work but very, very slowly. It was structured "For each Cell in Range..." As there are 2000 rows and 198 columns in the range, I think that it was being slowed down by having to go through so many cells.

Does anyone know how I can use VBA for validations on a 2000 x 198 array without slowing the file down?

The validations would all be for whole numbers between 0 and x, but x varies in different columns.

If anyone wants the range to play with in their macros, it is E4:GT2003.

Thanks.

 

[lrobbo314]

What is the validation? You said that it is for whole numbers between 0 and x. But, what is the criteria?

 

[EdwardAveyard]

If I type below what I've done so far, you should get the idea. This code causes everything to run very slowly, and it doesn't even cover the whole template yet. (I'm a novice at VBA.)

You can see that these are all validations for whole numbers, all warnings and all have formula1 (the lower bound of the valid range) as 0. It's formula2 (the upper bound of the valid range) that varies.

Sub TryAgain()


Dim ws As Worksheet
Dim range1, range2, range3, range4, range5 As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Data Entry")


Set range1 = ws.Range("AU3:BF2306")
Set range2 = ws.Range("BG3:BL2306")
Set range3 = ws.Range("AI3:AT2306")
Set range4 = ws.Range("bs3:cj2306")
Set range5 = ws.Range("k3:ah2306")


With range1.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
Formula1:="0", Formula2:="10"
End With


With range2.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
Formula1:="0", Formula2:="14"
End With


With range3.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
Formula1:="0", Formula2:="3"
End With


With range4.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
Formula1:="0", Formula2:="3"
End With


With range5.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
Formula1:="0", Formula2:="100"
End With


End Sub

 

[lrobbo314]

I tested the code and it went too fast to measure. It was almost instantaneous. But, I just put whole numbers in all of the ranges. If you have a bunch of formulas in your worksheet, it could be slowing it down. The following small tweaks might make it go faster.

Sub TryAgain()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim ws As Worksheet
Dim range1, range2, range3, range4, range5 As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")


Set range1 = ws.Range("AU3:BF2306")
Set range2 = ws.Range("BG3:BL2306")
Set range3 = ws.Range("AI3:AT2306")
Set range4 = ws.Range("bs3:cj2306")
Set range5 = ws.Range("k3:ah2306")

'range1.Value = Evaluate("=row()")
'range2.Value = Evaluate("=row()")
'range3.Value = Evaluate("=row()")
'range4.Value = Evaluate("=row()")
'range5.Value = Evaluate("=row()")

With range1.Validation
 .Delete 'delete previous validation
 .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
 Formula1:="0", Formula2:="10"
End With


With range2.Validation
 .Delete 'delete previous validation
 .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
 Formula1:="0", Formula2:="14"
End With


With range3.Validation
 .Delete 'delete previous validation
 .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
 Formula1:="0", Formula2:="3"
End With


With range4.Validation
 .Delete 'delete previous validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
 Formula1:="0", Formula2:="3"
End With


With range5.Validation
 .Delete 'delete previous validation
 .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertWarning, _
 Formula1:="0", Formula2:="100"
End With

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