Delete rows on a protected sheet

Copper Contributor

I am using Microsoft Excel version 2311

I created a document, entered data in it and protected it from the review tab.

The options that I allowed for anyone who gets access to this worksheet (without unprotecting it) are:

1. Select Locked Cells

2. Select Unlocked Cells

3. Insert Rows

4. Delete Rows


The problem that I am facing right now is that when I am trying to insert a row, a new row is inserted, but when I am trying to delete a row, the row is not being deleted. Instead a dialog box appears saying "You are trying to delete a row that contains a locked cell. Locked cells cannot be deleted while the worksheet is protected. To delete a locked cell, first remove protection using the unprotect sheet command (Review Tab, Changes Group). You may be prompted for a password."

Am I wrong somewhere? Have I missed something?

Please let me know a solution to this.

1 Reply


As the error message indicates, you can only delete a row if all its cells are unlocked - otherwise it would defeat the protection.

The alternative is to create a macro that:

  1. Unprotects the sheet.
  2. Deletes the selected row(s).
  3. Protects the sheet again.

The macro can be assigned to a custom keyboard shortcut and/or a Quick Access Toolbar button.

Users would have to allow macros when they open the workbook.