| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete cell value if there is data validation error
Thanks Rick , this works , appreciate your help
"Rick Rothstein" wrote: Change the Selection to UsedRange.... Sub DeleteInvalidData() Dim C As Range For Each C In UsedRange If C.Validation.Value = False Then C.Clear Next End Sub -- Rick (MVP - Excel) "kay" wrote in message ... thanks thats what i want, sorry i just get stuck in too many things . when i saw your message some how i did not see that part. thanks a lot again, i have just quick question if i dont want to select the range and look for that in whole sheet2 then colud you please help me to wirte that statement, "Rick Rothstein" wrote: I'm just wondering if you were able to see my previous response in this thread? I ask because I posted a macro which does what I think you appear to still be asking for. -- Rick (MVP - Excel) "kay" wrote in message ... Thanks i am using this formula to update value from sheet 1 where it reades cell value from sheet 1 and compare with sheet2 if there is any change then update the value. readcells3 < readcellsc3 Then ActiveCell.Value = readcells3 so that works fine. data validation error does not happen frequently but sometimes it happens and thats why i need to run some kind of macro which just delete the cell value. so to run another macro it wont give error . appreciate your help. "Orion Cochrane" wrote: If you are doing a straight copy-&-paste between Sheet1 (no validation) and Sheet2 (with validation), you are copying Sheet1's properties as well as contents. Therefore, Sheet2 will not have data validation if Sheet1 doesn't. Try pasting as values instead so you can keep Sheet2's data validation. <This is hopefully the part where someone who knows how to Paste Special in VBA reads this and replies with how to Paste Values, as I do not know how to do that -- Please rate posts so we know when we have answered your questions. Thanks. "kay" wrote: Thanks for your quick response. Actually what i am doing , i am comparing two sheets - sheet1 with sheet2 my input is in sheet1 and i update sheet2 by comparing sheet1. i dont know some how it copies the value of sheet1 into sheet 2 even if there is data validation. For eg Phone number in sheet 2 is number format. ( Sheet 1 does not have data validatation and dont want to put validation for that column for some reason) So incase if some one enters text in sheet 1 , it will copy that value and add/update to sheet2 (when i run that compare macro). so i want to check if there is invlalid data in the sheet 2 then delete that data and leave cell value as blank. Hope i explained well. 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! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation - Prevent delete | Excel Discussion (Misc queries) | |||
| 1004 Cell Protected Error for data validation lists when locked and protected | Excel Programming | |||
| Invalid Validation if cell "Delete"d | Excel Worksheet Functions | |||
| question data validation and delete/clear behavior | Excel Programming | |||
Threaded Mode
