Trouble deleting rows in a table

Hi!

I have a worksheet that has a defined table with 501 rows (first one is a header row) and 11 columns. There are two more tables on the sheet as well. The first table, the one I'm concerned about, is a list of “dummy” employees and related information.

I’ve uploaded it here:

https://drive.google.com/file/d/1ZEw3aNUwiBgc5B38Ulf3vqi1ZbzY18ln/view?usp=sharinghttps://docs.google.com/spreadsheets/d/13TwhUshguiV8eUYjpXbm8eswhlN5YbUZ/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true

Column A has this formula in each cell starting with A2 with the row number increasing by one in each subsequent cell (from A2 through A501 – A1 is the header).

=IF(B2<>"", B2&" "&C2&" "&E2, "")

The worksheet will be protected when the end user is using it with column A locked.

I need the end user to be able to remove an employee and all info in the employee row (columns B through K):

Without deleting a row from the table

Without adding a blank row to the table, and

Keeping the order of the list alphabetical (based on column B)

Not delete the formulas in protected column A

With the spreadsheet protected I can’t delete a row so no problem there.

With the spreadsheet protected I can “clear contents” of a row (I select B through K then “clear contents” and that automatically clears A as well) so no problem with removing the employee but doing so leaves a blank row in the middle of the list.

With the spreadsheet protected, when I highlight and cut/copy all the employee rows below the newly blank row and then paste them starting with the first blank row (in an effort to eliminate the blank row), I generate a #REF error in column A.

I added a VBA module (I hope that's the correct terminology), 2 actually because I'm doing this for two tables on the sheet but I'm having trouble only with the EE_DB table. The code for the table and module I'm having trouble with is this:

Sub RemoveEmployeeAndSort()
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Tables1") ' Replace with your sheet name
    Set tbl = ws.ListObjects("EE_DB") ' Replace with your table name

    ' Find the last row with data in the table
    lastRow = tbl.ListRows.Count

    ' Loop through the table rows in reverse order
    For i = lastRow To 2 Step -1
        If tbl.ListRows(i).Range.Cells(1, 1).Value = "" Then
            ' Clear contents if it's a blank row
            tbl.ListRows(i).Range.ClearContents
        End If
    Next i

    ' Sort the table using VBA
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=tbl.ListColumns(1).Range, SortOn:=xlSortOnValues, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With

    ' Reapply protection without a password
    ws.Protect UserInterfaceOnly:=True
End Sub

When I run it (spreadsheet protected) it gives me a 1004 error and when unprotected it deletes my formulas from column A. 

Is it possible to do what I'm trying to do?

Thank you so much!! 🙂 🙂

Diane