I am running Excel (M365 version 2211) on Windows 11 version 22H2.

In my spreadsheet there is a table (named "ToDeleteFromDL") for which I cannot delete rows. When I right click on a cell of the table the only options I am presented is to delete the column (which I don't want) or the entire sheet row (which I don't want either). Attached a screenshot of the right-click menu.

The same happens in the code:

Dim ToDelete As ListObject
Set ToDelete = Sheets("Diff").ListObjects("ToDeleteFromDL")

'Delete all rows from ToDeleteFromDL table except header
With ToDelete
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If
End With

When I run this code, a message pops out asking "Delete entire sheet row"? To which, again, I have to answer 'Cancel', because I just want to delete the table rows.

This code used to run up to a few days ago: the only thing that comes to my mind is that there has been an update of Office installed recently.

Does anybody have the same problem or know of a issue with the update?

Thank you in advance

[]

CodePudding user response：

Delete Excel Table's Data Body Range

With ToDelete
    If Not .DataBodyRange Is Nothing Then
        If .ShowAutoFilter Then ' is turned on
            ' Clear existing filters.
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        'Else ' is turned off; do nothing
        End If
        ' Delete.
        .DataBodyRange.Delete xlShiftUp
    'Else ' is already deleted; do nothing
    End If
End With

What's with the complications?

• You cannot delete rows when the table is filtered.
• An error will occur if you use .AutoFilter.FilterMode when the auto filter is turned off (no arrows).
• I have tried with xlShiftToLeft when the whole table is gone without any warning (alert). The documentation states "If this argument is omitted, Microsoft Excel decides based on the shape of the range.". It doesn't mention Excel tables in particular. Can we trust Excel to make the right decision? Possibly but why take a chance?