How to insert or delete rows in protected sheet? 

When working with sensitive or shared data in an Excel worksheet, protecting the sheet is a commonly used method to avoid accidental or unauthorized changes. However, by design, sheet protection also restricts certain operations, such as inserting or deleting rows, especially in cells that are locked. Many users often encounter the problem where they need to allow editing, like inserting or deleting rows, while still keeping the rest of the worksheet protected. This tutorial illustrates practical ways to insert or delete rows on a protected sheet and discusses their application scenarios, precautions, and alternative solutions to help you manage your data securely and efficiently.

Insert or delete rows in protected sheet

VBA: Temporarily unprotect, insert/delete, and reprotect automatically

Insert or delete rows in protected sheet

This solution is best suited for situations where you want users to have limited editing rights, such as inserting or deleting rows, while keeping the majority of your worksheet protected. It allows specified rows or sections of data to remain flexible and editable, while the rest of the sheet is secured from accidental changes or deletions.

Follow these step-by-step instructions to enable row insertion and deletion in a protected sheet:

1. Select the entire rows that you want users to be able to insert or delete. This process determines where in your protected worksheet these operations will be allowed. After selecting the rows, right-click on the selection, then choose Format Cells from the context menu. See screenshot:

2. In the appearing Format Cells dialog, navigate to the Protection tab. Here, uncheck the Locked box to unlock these specific cells or rows. Unlocking is essential; otherwise, users still won't be able to make changes even with other permissions. Be cautious not to unlock sensitive areas you want to remain protected. See screenshot:

3. Click OK to close the dialog box. Next, go to the Excel ribbon, click Review and then select Protect Sheet to enable worksheet protection. See screenshot:

4. In the Protect Sheet dialog that appears, you will see a list labeled Allow all users of this worksheet to. Check the Insert rows and Delete rows options. Enter a password to protect the sheet, if needed, and confirm it when prompted. This step is crucial for granting only the desired editing permissions to users while securing the rest of the sheet. See screenshot:

5. Finally, click OK to enable sheet protection under the new settings. As a result, only the specified rows can now be inserted or deleted, according to the permissions and unlocks you configured, while the rest of the worksheet remains unaffected. See screenshot:

This method works well for most basic needs but note that it does not allow users to insert or delete rows outside of the unlocked, permission-granted areas. Always review which cells are unlocked before enabling protection to avoid unintentional data exposure or inadequate protection.

If you need to allow insertion or deletion throughout wider sheet regions, or want more automation, consider VBA-based solutions below.

VBA: Temporarily unprotect, insert/delete, and reprotect automatically

This VBA macro approach is ideal for users who need to insert or delete rows frequently in a protected worksheet without manually unlocking and relocking the sheet each time. By running a macro, you can temporarily unprotect the sheet, perform row manipulations, and then have the sheet automatically protected again—all at the click of a button. This is especially suitable for collaborative or recurring tasks, ensuring data is both flexible and continually secured.

Main advantages of this approach include minimizing accidental exposure of sensitive data by limiting the unprotected window, and avoiding the hassle of repeatedly entering passwords for routine editing. However, a potential limitation is the need to have macros enabled, and basic macro security understanding is required.

1. Open the worksheet where you want to enable this functionality. Click the Developer tab (if the Developer tab is not visible, you can enable it via File > Options > Customize Ribbon), and then click Visual Basic. In the VBA editor window that appears, select Insert > Module, and paste the following code into the module:

Sub InsertRowInProtectedSheet()
    Dim ws As Worksheet
    Dim pwd As String
    Set ws = ActiveSheet
    pwd = InputBox("Enter sheet password:", "KutoolsforExcel")
    
    If pwd = "" Then Exit Sub
    
    On Error Resume Next
    ws.Unprotect Password:=pwd
    
    If Err.Number <> 0 Then
        MsgBox "Incorrect password or unprotect failed!", vbExclamation
        Exit Sub
    End If
    
    On Error GoTo 0
    Dim insertRow As Integer
    insertRow = Application.InputBox("Enter row number to insert:", "KutoolsforExcel", Type:=1)
    
    If insertRow > 0 Then
        ws.Rows(insertRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        MsgBox "Row inserted at " & insertRow, vbInformation
    End If
    
    ws.Protect Password:=pwd, AllowInsertingRows:=True, AllowDeletingRows:=True
End Sub

Sub DeleteRowInProtectedSheet()
    Dim ws As Worksheet
    Dim pwd As String
    Set ws = ActiveSheet
    pwd = InputBox("Enter sheet password:", "KutoolsforExcel")
    
    If pwd = "" Then Exit Sub
    
    On Error Resume Next
    ws.Unprotect Password:=pwd
    
    If Err.Number <> 0 Then
        MsgBox "Incorrect password or unprotect failed!", vbExclamation
        Exit Sub
    End If
    
    On Error GoTo 0
    Dim delRow As Integer
    delRow = Application.InputBox("Enter row number to delete:", "KutoolsforExcel", Type:=1)
    
    If delRow > 0 Then
        ws.Rows(delRow).Delete
        MsgBox "Row " & delRow & " deleted.", vbInformation
    End If
    
    ws.Protect Password:=pwd, AllowInsertingRows:=True, AllowDeletingRows:=True
End Sub

2. Close the VBA editor. To insert a row, press Alt + F8 to open the Macro dialog, select InsertRowInProtectedSheet, and click Run; to delete a row, select DeleteRowInProtectedSheet and click Run. You will be prompted to enter the worksheet password and specify which row to insert or delete. This method reprotects the sheet immediately after the operation to ensure continuous security. Be sure to save your workbook as a macro-enabled file (.xlsm) for future use.

Tips:

• Always confirm you have the correct password before running the macro, as an incorrect password will block the automation.
• If any error occurs, review input values and ensure macros are enabled in your Excel Trust Center settings.