Cant delete multiple rows from a filtered table with a hidden column

[Steve Bayliss]

Hi Everyone,

This is an interesting problem and I think it is a bug.

I have a structured table (listobject) that has a hidden column(s).

When the table is filtered I cannot delete multiple rows. If I unhide the hidden column I can.

[TABLE="width: 515"]
<tbody>[TR]
[TD]

To demonstrate the issue setup a structured table as follows:​

[/TD]
[/TR]
</tbody>[/TABLE]
Make sure you start in Column A

[TABLE="width: 515"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[/TR]
[TR]
[TD]bla bla[/TD]
[TD]bla bla[/TD]
[TD]ABC[/TD]
[TD]bla bla[/TD]
[/TR]
[TR]
[TD]bla bla[/TD]
[TD]bla bla[/TD]
[TD]DEF[/TD]
[TD]bla bla[/TD]
[/TR]
[TR]
[TD]bla bla[/TD]
[TD]bla bla[/TD]
[TD]ABC[/TD]
[TD]bla bla[/TD]
[/TR]
[TR]
[TD]bla bla[/TD]
[TD]bla bla[/TD]
[TD]ABC[/TD]
[TD]bla bla[/TD]
[/TR]
[TR]
[TD]bla bla[/TD]
[TD]bla bla[/TD]
[TD]ABC[/TD]
[TD]bla bla[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 515"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

To demonstrate the issue setup a structured table as follows:​

[TABLE="width: 0"]
<tbody>[TR="bgcolor: transparent"]
[TD]Hide column for Column1
Filter on Column3 for ABC


Select more than one row and try to delete rows..[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Error: This won't work because it will move cells in a table on your worksheet<strike></strike>[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Unhide Column1
Hide Colulm4

Error: Can't move cells in a filtered range or table

Unhide all columns
Can delete multiple rows fine.[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>Why?? Bug??


OK in doing this post I appear to have now found a couple of workarounds...

1. Instead of selecting entire rows, select a range of cells in the table for the rows to delete then right click, delete, Entire Sheet Row... which works.. go figure.


2. Add a blank column into the worksheet so that the table starts in Column B. repeat above and you can delete multiple filtered rows with columns in the table hidden.


I don't really want an additional column prior to the table and I would like to essentially do something like this in VBA after filtering to delete all rows matching a criteria...

Range("Mytable").EntireRow.Delete

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks Steve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pwhatley]

I experienced the same issue when deleting filtered rows in a VBA module, after I had gone back later to hide some columns. It took a while to figure out what was causing it. If remember correctly, my work around in VBA was to display the columns, delete the rows, then re-hide the columns.

Best!