Give this a try...

Sub DeleteInvalidData()
Dim C As Range
For Each C In Selection
If C.Validation.Value = False Then C.Clear
Next
End Sub

where you would obviously select the cells you want to test before running
it.

--
Rick (MVP - Excel)


"Orion Cochrane" wrote in message
...
Unlike Access, which detects invalid data in existing data when data
validation is in place, Excel doesn't do that. If this person has
pre-existing data and then put data validation in place after the fact, it
will be in force for new data or when you want to change existing data
that
already doesn't comply with the validation rules. I actually like that
Excel
doesn't behave like Access in this instance so I can manually correct the
invalid data.
--
Please rate posts so we know when we have answered your questions. Thanks.


"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave
blank in
that cell)
so when i run this macro it should check for data validation and if
there is
an error
say date field is entred incorrectly then it should delete that cell
value
and leave the value blank.

Thanks a lot in advance!