Trouble deleting rows in a table

Brass Contributor

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.googl...

 

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

1 Reply

@DianeDennis 

It is possible to achieve what you are trying to do. The error you are encountering is likely due to the protection settings of the worksheet. Since you want to delete rows and sort the table using VBA, you need to temporarily unprotect the worksheet to perform these actions and then protect it again after making the changes.

Here is an updated version of your VBA code that should work correctly:

vba code (untested:(

 

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

    ' Unprotect the worksheet to make changes
    ws.Unprotect

    ' 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

    ' Reprotect the worksheet without a password
    ws.Protect UserInterfaceOnly:=True
End Sub

 

Make sure to replace "Tables1" with the actual name of your worksheet, and "EE_DB" with the name of your table. Also, ensure that the worksheet is protected with the "UserInterfaceOnly" parameter set to "True" to allow VBA to make changes while the worksheet is protected.

After running this code, the blank rows should be removed from the table, and the remaining rows should be sorted alphabetically based on column B, without affecting the formulas in column A.

The text, steps and Vba Code were created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.