How do I delete rows without deleting validation?

[Missi Broussard]

I have a long spreadsheet with multiple dependent lists. If a change is made to the spreadsheet such as deleting a row, the validation is removed. I have to re-do the validation each time I make a change. Is there a way to "lock" the validation so that whatever changes are made to the spreadsheet, the validation stays?
Thanks, and hope I'm using the correct terminology for all this!

 

[Fazza]

Your terminology is great. AFAIK the validation will not persist.

If it initially extends for many, many rows maybe row deletion will still leave enough rows of validation. However, normal approach is so use program code (VBA) to re-instate/refresh data validation/formats/etc. Maybe also protect the worksheet to limit users's access to delete rows?

I'm assuming the ranges of data feeding into the validation list are somewhere safe - like on a dedicated (maybe hidden) worksheet, and your concern is with the cells that users enter data.

regards

 

[Missi Broussard]

Thank you Fazza. I think limiting the user's access may be the best bet. I have very limited knowledge of VBA, although did use it successfully a few times. Thanks for your help!
Missi

Your terminology is great. AFAIK the validation will not persist.

If it initially extends for many, many rows maybe row deletion will still leave enough rows of validation. However, normal approach is so use program code (VBA) to re-instate/refresh data validation/formats/etc. Maybe also protect the worksheet to limit users's access to delete rows?

I'm assuming the ranges of data feeding into the validation list are somewhere safe - like on a dedicated (maybe hidden) worksheet, and your concern is with the cells that users enter data.

regards