error while deleting a sheet row Runtime error '1004': Delete method of range class failed

Question

I'm using excel vba to delete filtered rows. The code is working just when I specified the range to be on A1 and put my table headers on A1. But, my table headers on B9 so I need to put it on the range but that error occurs. I didn't know why its working for Range("A1") and it didn't work for Range("B9"). In addition when I put A1 as my range to my table it deleted all the rows not just the filtered rows.

Sub Delete_CD_Blanks()


Dim Rng As Range
Dim Rng_Del As Range


Set Rng = Range("B9").CurrentRegion

If Sheets("tt").AutoFilterMode = True Then
    Sheets("tt").AutoFilter.ShowAllData
End If

   ' Rng.AutoFilter field:=4, Criteria1:=” = ”
Rng.AutoFilter field:=6, Criteria1:="??? ?????"

Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Sheets("tt").AutoFilterMode = False

End Sub

Ok I understand. yes Range("B10") not visible its not included in my filtering. So how can I fix it to make it delete the visible rows? — Mee, Jul 13 '21 at 09:24
Please mention the error line. Also check `Range("B9").CurrentRegion` area an sheet that contains it.. — Naresh, Jul 13 '21 at 09:26
@Naresh Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete the error in this line, I check the area and its correct — Mee, Jul 13 '21 at 09:27
[Refer this similar question on SO](https://stackoverflow.com/questions/34003403/rangecustomtable-specialcellsxlcelltypevisible-delete-now-fails-run-time) — Naresh, Jul 13 '21 at 09:34
@Naresh Now its appear that the error on the filter line Rng.AutoFilter field:=6, Criteria1:="??? ?????" — Mee, Jul 13 '21 at 09:36
Are there visible rows above the 9th row? Is the 9th row the one keeping headers? Do you want deleting all visible cells, except the headers row? — FaneDuru, Jul 13 '21 at 10:18

score 0 · Answer 1 · answered Jul 13 '21 at 10:50

You did not ask my clarification questions and I (only) suppose that your problem stays in the fact that there are some cells above the 9th row, which make part from the CurrentRegion. If I am right, please add a code line, able to create a range starting from 9th row (inclusive):

  'your existing code
  Set rng = Range("B9").CurrentRegion 'existing in your code
  Set rng = Intersect(rng, ActiveSheet.rows("9:" & rng.rows.count)) 'it creates a range slice, starting from 9th row and ending to the `CurrentRegion` last row
  'your existing code

error while deleting a sheet row Runtime error '1004': Delete method of range class failed

1 Answers1