Caused when inserting rows under as yet unknown conditions
After much editing of a mid-size worksheet, including inserting and or deleting rows, you notice data validations (DV) and comments (C) have not followed the cell into which they were originally entered.
Perhaps there is a programmatic method to relocate data validations and comments, but herein is described an involved manual process to move data validations and comments back to the cells they were intended for.
To realign Data Validations and Comments along with their corresponding text, formulas, & formatting
- If at anytime you encounter ‘error while working with document’ or the workbook becomes unresponsive, close the workbook WITHOUT SAVING. This means constantly saving your document after successful edits. Error seemed to be caused by attempting to copy/paste too many rows.
- Create and work on a copy of the sheet with incorrectly located or shifted DV & C. This will maintain your defined names.
- In the working workbook copy, add a ‘recovery’ sheet.
- Copy rows with DV & C in ‘broken’ sheet. Into ‘recovery,’ in column A, at the row index corresponding to ‘broken’ related text, paste ‘Formulas without borders’
- Copy rows with related text in ‘broken’. Into same row in ‘recovery,’ first ‘Paste formula only’ and then ‘Paste only formatting’ which clobbers comments.
- Finally, again copy rows with DV & C in ‘broken.’ Then in ‘recovery,’ paste special ‘Comments’ to appropriate row index.