Delete a Row of a Protected Sheet.... Possible???

Hardware Man

New Member
Joined
Apr 10, 2013
Messages
40
I'm developing a spreadsheet which has about 40 columns or so. Half of them are formulas that I want to protect, the other half are cells that the user needs to fill in and are to remain editable. The number of used rows will vary greatly, depending on the user.

The formulas are written in such a way as to always display a blank cell if the user did not enter his data where he's supposed to. This enabled me to copy down about 1000 rows, even though most users will only be using about a hundred rows at the most.

What I'd like to do is protect the cells that I don't want the user to mess with, while enabling him to delete an entire row if that data is no longer needed. I don't want him to be able to insert rows, just delete them.

Is this possible?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Yes it's possible. When you protect the sheet there is a Protect Sheet dialog box that opens. Tick the box next to Delete Rows.
 
Upvote 0
Yes it's possible. When you protect the sheet there is a Protect Sheet dialog box that opens. Tick the box next to Delete Rows.

Not sure what I'm doing wrong, but I attempted to do just that before I posted the question and gave it yet another try after reading your response. I get the standard "can't do it because one or more cells is protected" pop up. To be on the safe side, I went so far as to check EVERY box in the "Protect Sheet" dialogue box.

The result is, I can insert a row but I cannot delete one (or do anything else that affects a protected cell for that matter). Any idea what I could be doing wrong?
 
Upvote 0
Try unlocking the rows for which deletion is permissible before you protect the sheet.
 
Upvote 0
Try unlocking the rows for which deletion is permissible before you protect the sheet.

Just tried that. Yeah, that allows me to delete the row, but only because the act of unprotecting the rows also unprotects the cells that contain formulas at the same time, which is what I'm out to avoid. Unless I'm missing something.
 
Upvote 0
Just tried that. Yeah, that allows me to delete the row, but only because the act of unprotecting the rows also unprotects the cells that contain formulas at the same time, which is what I'm out to avoid. Unless I'm missing something.

Are you saying you want the user to be able to delete rows that have formulas in them, but only if all the formulas in those rows are returning "" (appear blank)?
 
Upvote 0
No. Quite the opposite. The spreadsheet is for calculating the profitability of products. If a user wants to get rid of a product, he's simply delete the entire row. By my initially "copying down" more than enough rows than they'd ever need, it's not a problem if some rows (and their formulas) later get deleted.
 
Upvote 0
No. Quite the opposite. The spreadsheet is for calculating the profitability of products. If a user wants to get rid of a product, he's simply delete the entire row. By my initially "copying down" more than enough rows than they'd ever need, it's not a problem if some rows (and their formulas) later get deleted.
Sorry, but I'm confused. In post #5 you said:
Yeah, that allows me to delete the row, but only because the act of unprotecting the rows also unprotects the cells that contain formulas at the same time, which is what I'm out to avoid.
which seems contrary to the part of your reply I made bold red above.
 
Upvote 0
Sorry for the lack of clarity on my part, and I do very much appreciate your offer to help. Here's an example...

Lets say that a user fills in 100 rows with product data, and later on, he decides he no longer wants to offer the product that appears in row #32.

While he could delete the data in the unprotected cells of row #32, that would leave a bunch of blanks. Not very attractive or useful. So in this example, I just want the user to be able to delete ALL of the cells in row #32 (formulas and all). Not to "re-confuse" things, but what I meant above was since the sheet has 1000+ cells with formulas already copied down (but appearing as "blank cells" to the user), it doesn't hurt the sheet if a single row gets deleted here and there.

Another alternative would be if I had the ability to use the "sort filter" on a protected sheet. If that were possible, the user could simply delete the editable data of a product he no longer wants to evaluate, and then sort the sheet by "product name", which would force any blanks to the bottom of the list. But it seems that that is not possible either (at least in the experimenting I've conducted).
 
Upvote 0
Sorry for the lack of clarity on my part, and I do very much appreciate your offer to help. Here's an example...

Lets say that a user fills in 100 rows with product data, and later on, he decides he no longer wants to offer the product that appears in row #32.

While he could delete the data in the unprotected cells of row #32, that would leave a bunch of blanks. Not very attractive or useful. So in this example, I just want the user to be able to delete ALL of the cells in row #32 (formulas and all). Not to "re-confuse" things, but what I meant above was since the sheet has 1000+ cells with formulas already copied down (but appearing as "blank cells" to the user), it doesn't hurt the sheet if a single row gets deleted here and there.

Another alternative would be if I had the ability to use the "sort filter" on a protected sheet. If that were possible, the user could simply delete the editable data of a product he no longer wants to evaluate, and then sort the sheet by "product name", which would force any blanks to the bottom of the list. But it seems that that is not possible either (at least in the experimenting I've conducted).

Thanks for the clarification. As you've discovered, the user cannot delete an entire row if any of the cells in that row are locked and the sheet is protected. One possible workaround would be to provide a macro button on the sheet which the user can click after selecting the row(s) to be deleted. The macro code could produce a pop-up asking the user to confirm it is safe to delete the row(s). If that's of interest, I can provide the code.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top