Excel: unable to delete a table row

Question

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

Here below a screnn-shot 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

Answer 1

Could you check if there is any filter in your table, if it is, it will cause that problem, But you can still use the delete option in top bar.

Answer 2

Hi msartoris24,

Greetings! Thank you for posting to Microsoft Community.

See the next post.

Edit by Snow [2022-12-20 19:42]

Best regards,

Snow Lu

Answer 3

OK, you have something funky happening. The screen capture you showed is an unexpected mix

When I select the delete option inside an area defined as an Excel Table, I get the following

(Example from 2013, may be visually slightly different than 365)

I don't know how you managed to get the mix of options.

The simplest way to find a solution is if you share and example file with us. Take your problem file, cut out any private or sensitive information and just leave a single tab. In it write instructions describing the EXACT steps / commands you use to encounter the problem (we need to be able to recreate the problem.

Upload Example File - Share via “Personal” OneDrive

There is no way to upload example files directly to this forum.
.

Trouble shooting problems using this text only forum can be like a visit to the dentist without anaesthetics: a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we hopefully can eventually come up with a / “the” solution.
.

Often it is faster and easier for everyone if we have a “sample file(s)” so we can get "hands on", to look at, and to “play with” your data. There are a couple of advantages of providing example files:

. * we have exactly the same data you are describing,

. * if your problem requires merging multiple files, we can work with that

. * you will often get different suggestions from users with points of view and experience.
.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:
https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156
.

The above article includes links to a macros to randomize existing text in Word and Excel
.

Share OneDrive files or Folders with a link

https://support.microsoft.com/en-us/office/share-files-and-folders-in-onedrive-personal-3fcefa26-1371-401e-8c04-589de81ed5eb
.

Note: make sure to upload to your Personal (consumer) OneDrive rather than your work OneDrive or SharePoint. Files in the Work cloud can be hard to share due to security restrictions placed by the work admins. Or use any other free storage service (anything other than business OneDrive or SharePoint).
.

Another article explaining need for sharing example files:               

Why a sample file is important for troubleshooting. How to do it. https://answers.microsoft.com/en-us/msoffice/forum/all/why-a-sample-file-is-important-for-troubleshooting/9441ae3c-1e92-41c6-9a1f-5b377b08e5a5
.

How Much Cloud Storage Space Will You Really Use?https://www.howtogeek.com/822046/how-much-cloud-storage-space-will-you-really-use/
Fergus O'Sullivan    @FergusOSullivan            Aug 5, 2022
If you’re shopping for cloud storage, one of the big decisions you’ll make is how much of it to pay for. Starting at free plans for a few gigabytes to expensive subscriptions that offer several terabytes of storage space; you have a lot of choice.
.

2022 03 15- The 5 Best Free Cloud Storage Services
https://www.howtogeek.com/787191/best-free-cloud-storage-services/
There’s no shortage of great options if you’re looking for free cloud storage. Plenty of companies will let you store several gigabytes in the cloud, and all you need to do is create an account. We’ve put together our five favorites.
.  *  Google Drive      15GB
.  *  MS OneDrive (free) 5GB
.  *  Mega                     20GB (first year)
.  *  IceDrive               10GB
.  Sync.COM              5GB
.

Answer 4

I didn't see the filter since it was on the rightmost column outside the screen. Removing the filter fixed the problem.

Thank you.

Answer 5

I managed to get what you call a "mix of options" by setting a filter on one of the table columns. That was the problem and now I fixed it. I also added a line to remove the filter before deleting the content of the table, to be sure I don't forget.

Set ToDelete = Sheets("Diff").ListObjects("ToDeleteFromDL")

'Delete all rows from ToDeleteFromDL table except header

With ToDelete

If Not .DataBodyRange Is Nothing Then 

    .Range.AutoFilter ' Turn off the filter 

    .DataBodyRange.Delete 

End If 

End With