Validation of all cells in a column? 2

[notuserfriendly]

Hi
I'm trying to write a sub or function that handles
validation on all cells in a specific column.
I would like to skip the hardcoding as in the example

With Range("e5").Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:=xlValidAlertInformation, _
        Minimum:="5", Maximum:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

And change the range to a column, but the range only accepts it in this format, when trying it with Columns it doesn't work since the validation requires a range.
Is there a way to code it so that Excel knows which is the last row? without using a findlastrow?

Anybody?

 

[AnotherHiggins]

Simply change
Range("e5").Validation
to
Range("e[red]:e[/red]").Validation

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[notuserfriendly]

Ok, tested it but don't know if it works since
I get an error on compile
it says "Minimum:=" arg doesn't exist.

Thanks anyway

 

[PHV]

With Range("e:e").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[notuserfriendly]

Strangely i get an error 1004 when trying to run this code (yes, added the end with). I can see examples like this on my Excel 2002 SP3, I'm wondering if I've missed something, is it only 2003 compatible or?

 

[notuserfriendly]

Ok, got it, this happends if I already created the object. Now I just have to find out how to know if there is an validation object there.

 

[notuserfriendly]

I just have one last question, how to remove the value of the cell afterwards is it done in the validation statement
or is it done afterwards by just clearing?

 

[notuserfriendly]

Don't bother, solved it.
Did it in the workbook.change