I have a worksheet that has thousands of cells containing data validation. I recently noticed that when I copy an entire row from the original worksheet to another worksheet (within the same workbook), the data validation in some of the cells change unexpectedly in the destination. The data validation was set to a named range of "=MyListA" on the original worksheet, and changed to "=MyListB" on the destination worksheet.
I confirmed the data validation window on the original cell. It still had the formula of "=MyListA". I pressed the 'Clear All' button in the data validation window, and it appeared to clear correctly from what was shown in the data validation window, but after closing the data validation window, the cell now had the data validation of "=MyListB". If I repeat the clear all procedure in the data validation window a second time, it will clear it out for real and then I can set it to what I want and everything works great. The cells in question would have been copied from another location and their data validation rules changed, but they retained the old rules in the background. I am curious if this is a known bug that anyone has seen or if you have an explanation for why this happened?
More importantly, does anyone have any ideas to test the worksheet to find other cells that may have the same issue? I did try using VBA to clear the data validation with Range("A1").validation.delete, but it actually does clear all validation on the first pass. I also tried using the macro recorder, but the steps recorded also clear out all levels in one pass when initiated from VBA. I was hoping that doing it this way on the entire sheet would leave me with sheet where any remaining data validation would signal the issue.
One more thing, if I copy the entire row or just the cell within the original worksheet, the issue is not apparent. However, it does occur when I copy it to another worksheet.
I confirmed the data validation window on the original cell. It still had the formula of "=MyListA". I pressed the 'Clear All' button in the data validation window, and it appeared to clear correctly from what was shown in the data validation window, but after closing the data validation window, the cell now had the data validation of "=MyListB". If I repeat the clear all procedure in the data validation window a second time, it will clear it out for real and then I can set it to what I want and everything works great. The cells in question would have been copied from another location and their data validation rules changed, but they retained the old rules in the background. I am curious if this is a known bug that anyone has seen or if you have an explanation for why this happened?
More importantly, does anyone have any ideas to test the worksheet to find other cells that may have the same issue? I did try using VBA to clear the data validation with Range("A1").validation.delete, but it actually does clear all validation on the first pass. I also tried using the macro recorder, but the steps recorded also clear out all levels in one pass when initiated from VBA. I was hoping that doing it this way on the entire sheet would leave me with sheet where any remaining data validation would signal the issue.
One more thing, if I copy the entire row or just the cell within the original worksheet, the issue is not apparent. However, it does occur when I copy it to another worksheet.
