Some rows can't be deleted

ALBIONE 1 Reputation point
2022-09-05T06:03:49.093+00:00

Hello,

for about a week I have an issue with one worksheet inside my excel file. The issue occurs when I try to delete a row. Before, it was done automatically using a macro and it worked without any issues, however since last week it wouldn't work and after running the macro I would get an error "Error 1004 Delete method of Range class failed" in line which supposed to be deleting entire row. I tried to delete the rows manually, however out of 3 rows only 2 rows would be completely deleted and the third row would only clear its contents, and I would not be able to delete it manually at all. The sheet is not protected, I tried many solutions and it wouldn't work (like deleting rows using key binds) , I also read that it may be caused by a header row, but I do not have any headers. The row number does not matter, excel is always stuck on the third row and other can clearing the cells it would not delete the row at all. While trying to delete it manually I do not get any errors - only when I run macro.

Code:

Set NameOps= Sheet5.Range("D35:D650")

For Each s In NameOps
If s.Value = "Z Emp Then
s.EntireRow.Copy
Sheet10.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
s.EntireRow.Delete <- This is where errors occurs
End If
Next

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,638 questions
{count} votes