Rows - .EntireRow.Delete - 1004 Error, but rows are deleted (1 Viewer)

Cosmos75

Registered User.
Local time
Today, 05:28
Joined
Apr 22, 2002
Messages
1,281
I have a simple delete code line that gives me a 1004 error (Delete method of Range class failed), BUT the rows are deleted! The error causes the remaining code not to execute because of the error handling code I have which give a msgbox explaining that an error code occurred the exits.

Any idea what is causing the error? The rows that are being deleted are the correct rows, rows 37245:38416.

Code:
.Rows(i & ":" & iEnd).EntireRow.Delete
 

MarkK

bit cruncher
Local time
Today, 03:28
Joined
Mar 17, 2004
Messages
8,178
You can selectively ignore this error with code like...
Code:
sub deleterows(rng as range, i as long, iEnd as long)
on error goto handler
   rng.Rows(i & ":" & iEnd).EntireRow.Delete

final:
   exit sub

handler:
   if err = 1004 then
[COLOR="Green"]      'selectively ignore this error[/COLOR]
   else
[COLOR="Green"]      'inform user about all other errors[/COLOR]
      msgbox err & " " & err.description & vbcrlf & _
         "Process failed.
   end if
   resume final
end sub
hth
Mark
 

Cosmos75

Registered User.
Local time
Today, 05:28
Joined
Apr 22, 2002
Messages
1,281
You can selectively ignore this error with code like...
Mark,

Thanks for the reply! That was going to be my next move if I can't figure out why I get the error.

Someone else told me to try changing it to this.
Code:
.Rows(i & ":" & iEnd).Delete

I'll test that as post back in case it helps someone else too.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:28
Joined
May 7, 2009
Messages
19,169
What is the Parent of. Rows, is it a Worksheet object?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:28
Joined
Feb 28, 2001
Messages
26,996
Error 1004 is one of those generic errors that says "Something went wrong but I'm not entirely sure why." My experience in this is somewhat limited, but usually I have solved these problems by not assuming the implied reference on the left-hand side of the dot that leads your intended action. Your code snippet looks as though it could have come from a WITH xx/END WITH block. But if it did not, then you are assuming something to be automatic that isn't.
 

Cosmos75

Registered User.
Local time
Today, 05:28
Joined
Apr 22, 2002
Messages
1,281
What is the Parent of. Rows, is it a Worksheet object?
Yes, you're correct.

Error 1004 is one of those generic errors that says "Something went wrong but I'm not entirely sure why." My experience in this is somewhat limited, but usually I have solved these problems by not assuming the implied reference on the left-hand side of the dot that leads your intended action. Your code snippet looks as though it could have come from a WITH xx/END WITH block. But if it did not, then you are assuming something to be automatic that isn't.
It's not the WITH/END WITH block since other code within it is working.

I still haven't had time to test changing .EntireRow.Delete to .Delete yet. The entire code takes about a long time to run so I just fixed the data manually for now since I needed to have it quick, and haven't been able to get back to it.

When I do find time to just run it and leave it alone, I'll post back the results so that it will hopefully save someone else with the same problem some time.
 

Users who are viewing this thread

Top Bottom