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

Steve Bayliss

New Member
Joined
Jan 13, 2016
Messages
8
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.

To demonstrate the issue setup a structured table as follows:

<tbody>
</tbody>
Make sure you start in Column A

Column1Column2Column3Column4
bla blabla blaABCbla bla
bla blabla blaDEFbla bla
bla blabla blaABCbla bla
bla blabla blaABCbla bla
bla blabla blaABCbla bla

<tbody>
</tbody>

To demonstrate the issue setup a structured table as follows:
Hide column for Column1
Filter on Column3 for ABC


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

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

Unhide all columns
Can delete multiple rows fine.

<tbody>
</tbody>
<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

Thanks Steve

<tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top