3

was wondering if anyone noticed a change in behaviour of the following or similar code:

.Range("CustomTable").SpecialCells(xlCellTypeVisible).EntireRow.Delete

I use it to delete filtered range on a ListObjects table in excel 2013 and until about last week it was working fine; and now, if there are at least two non-sequential lines needed to be deleted, it is throwing an error: "Run-time error '1004': Delete method of Range class failed. I am sure it is not just the case that nothing is visible in filtered data-set, I ran it in debug and it definitely has multiple lines to delete, and it does give a normal entire row address with multiple lines to delete, but it fails to.

I have solved it by stripping out EntireRow and suppressing excel alerts on confirmation menu if I want to delete entire row. I am just quizzed why it suddenly stopped working?

R3uK
  • 14,417
  • 7
  • 43
  • 77
Raugmor
  • 566
  • 5
  • 12
  • 1
    have you tried an `For Each` loop? its slow, but should work... and **just for debugging** have you tried to do each step with selection to check where it fails? like `.Range("CustomTable").SpecialCells(xlCellTypeVisible).Select` and then `Selection.EntireRow.Select` and then `Selection.Delete`. does everything work as desired? just to make sure its not the deletion itself that triggers the error (that should work) – Dirk Reichel Nov 30 '15 at 16:54
  • Probably too complex for excel to handle? Is it a lot of rows? – Rosetta Nov 30 '15 at 16:57
  • Tables are weird. If you try and delete a row in a filtered table using `Listrows(n).Delete` it simply fails silently. Maybe they're trying to fix it. :) – Rory Nov 30 '15 at 17:02
  • @KSSheon - I tested it with 3 rows on a table and it failed. It did `.Select` the `EntireRow` though. – Scott Holtzman Nov 30 '15 at 17:06
  • i'm confused... tried it in all ways i can imagne and got no errors... maybe a 'hotfix' i missed :D – Dirk Reichel Nov 30 '15 at 17:09
  • @DirkReichel it does fail in that case on the last one: `Selection.Delete`. any other actions work fine. @KS Sheon that's the thing, on excel 2013 until half way through last week it would work fine, i bet Ms installed some quick fix that now takes this action as un-doable. @Rory that never worked for me in past, so i never used this method anyhow – Raugmor Dec 01 '15 at 16:44
  • @DirkReichel do you have automatic Excel 2013 with automatic updates? i have a strong feeling they did in fact chuck in a crappy "hot fix".... – Raugmor Dec 01 '15 at 16:45
  • yes... its normally allways up to date... when manually selecting row 1 and 3 and then input `Selection.Delete` in direct window, did you still get the error? (simply select 2 random empty lines but in the sheet where you normaly run the macro)... if you get the error, try closing excel and then open a new workbook doing the same with an empty sheet... there is still the chance the workbook itself has an error... but thats rare and i only had that one time in my life... just for debugging... – Dirk Reichel Dec 01 '15 at 17:12
  • @DirkReichel i created brand new workbook, created new `ListObjects` table with just two columns, still, filtering on at least two non sequential rows, code fails and even if i manually select the two rows separately and enter `Selection.Delete` in immediate window it fails with the same reason. `Selection.Delete` on the same sheet, but below the table works fine on any number of non sequential rows – Raugmor Dec 02 '15 at 08:16

2 Answers2

1

I've come across this problem as well. What I've found to work is to save the range, remove the filter and then iterate through the areas of the range in reverse order (required as ranges change as you delete).

I've added in the "Application.Union" to take care of hidden columns. I just found a case of hidden columns creating multiple areas for the same row. So the solution to that is to get the SpecialCells range with the EntireRow, which still gives you duplicate areas for full rows. Then using the Application.Union you can compress these into a unique set of areas in a range.

Set delete_range = Application.Union(.Range("CustomTable").SpecialCells(xlCellTypeVisible).EntireRow, .Range("CustomTable").SpecialCells(xlCellTypeVisible).EntireRow)
.AutoFilter
Set delete_range =
For i = delete_range.Areas.Count To 1 Step -1
    delete_range.Areas(i).EntireRow.Delete
Next

Hope that works for you. The disabling alerts didn't solve my problems. Everything else I tried had corner cases that didn't work. I assume you've already catered for removing the header (if you have one) from the range of interest.

NOTE: I also had another strange case which was resulting in the range = nothing. Can't remember the reasons for this happening, but I also included a check for nothing of the range before processing. I didn't include that in this answer.

HangieMO
  • 26
  • 3
0

Just to clarify: I have found a work around it, therefore not looking for one, I am just trying to understand why this line worked OK previously and doesn't work now.

In case anyone stumbles into this post whilst searching for the solution the line can be replaced by the following 3 lines, giving same result:

   Application.DisplayAlerts = False
      .Range("CustomTable").SpecialCells(xlCellTypeVisible).Delete
   Application.DisplayAlerts = True

P.S. I am personally not a big fan of suppressing alerts, but feel like this is the most optimal fix...

Raugmor
  • 566
  • 5
  • 12
  • 1
    Sadly this workaround doesn't seem to work for me. This is a strange one, as the method works fine on other tables in my workbook **without a need for any workaround** - it just does what it's supposed to do - but it fails on this one table. EDIT: I was wrong - it does work - I was still using `.Entirerow.Delete`. When I switched to simply `.Delete` it works. – blackworx Jun 05 '17 at 08:32