Search code examples
vbaexcelexcel-2013

Range("CustomTable").SpecialCells(xlCellTypeVisible).Delete now fails. Run-time error '1004'

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?

Solution

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