Entirerow.delete Rows function Error 400 problem

[Devonair]

Hi guys,

I made this command, before it was working fine. I have been using it for quite sometime now.

Sheets("DATABASE").Rows("4:1048576").Delete

Now suddenly there is an error showing, I didn't change anything to my programming.

Error 400.

Tried this too in hope that it will get fixed.

Sheets("DATABASE").Rows("4:1048576").EntireRow.Delete

But no success.

I experimented with it, tried reducing the cells value to 4:1048000 It worked for a short while after a few calls... then it started its pop up Error 400 again.

Can anybody explain to me why is this happening. ?


Thanks again for the help.

 

[MARK858]

If you run the code below does it give any more info?

Sub Errcheck()
On Error GoTo Errorcatch
Sheets("DATABASE").Rows("4:1048576").Delete
Exit Sub

Errorcatch:
MsgBox Err.Description
End Sub

Btw, is there any reason why you are deleting so many rows?

 

[Devonair]

If you run the code below does it give any more info?

Sub Errcheck()
On Error GoTo Errorcatch
Sheets("DATABASE").Rows("4:1048576").Delete
Exit Sub

Errorcatch:
MsgBox Err.Description
End Sub

Btw, is there any reason why you are deleting so many rows?

When I run step by step It showed an error 1004 something...

I started using this procedure cause I noticed there are data's that are not seen I made sure all are deleted...
My file reached 10mb, after I accidentally discovered this, I made it a routine, my file size went down from 10mb to 600kb.
Since then When I refresh my sheet I use this method of deletion.

now I made it 4:10000 it works with no error. I just want to know why sometimes it works sometimes it don't. I want it to be all cells downward deleted to be very clean and sure like before.

 

[Devonair]

The error that shows after doing your code is

Can't push objects of the sheet.


Thanks

 

[MARK858]

an error 1004 something

The "something" part is important.

 

[MARK858]

The error that shows after doing your code is

Can't push objects of the sheet.


Thanks

See if the links below help

https://support.office.com/en-gb/ar...in-excel-559f37da-2b7f-4548-a58d-96669f5310d6

https://www.mrexcel.com/forum/excel-questions/1018034-can-not-delete-rows-tried-everything.html

 

[Devonair]

See if the links below help

https://support.office.com/en-gb/ar...in-excel-559f37da-2b7f-4548-a58d-96669f5310d6

https://www.mrexcel.com/forum/excel-questions/1018034-can-not-delete-rows-tried-everything.html

Ahh, ok this is the complete error popping up

Run-time error '1004':
Application-defined or object-defined error

 

[Devonair]

Thanks you for your links it worked there is an object way out there on my sheet >_<
I searched it using Ctrl+G, Special, Objects option then Tab one by one the object.

You are trully a life saver.

 

[Michael M]

You could also use

Sub MM1()
    Range(Rows("4:4"), Rows("4:4").End(xlDown)).EntireRow.Delete
End Sub

 

[Devonair]

Thank you sir for your advice
It's a great help