How do I delete rows without deleting validation?

Missi Broussard

New Member
Joined
Sep 3, 2013
Messages
19
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Sep 3, 2013
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,560
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top