Corrupting File (Row Insert / Delete) - methods to resolve

Hi,

I have an Excel file that has been in use now for about 2 years. It contains a reasonably large amount of data (currently c. 10mb) and macros.

Originally the file was created in 2003 and was converted to 2010 .xlsm file at the beginning of this year. It's been running fine, but a bit slow on row insert / deletion (appears to be a known issue after 2003 where you can't turn the calculation to manual).

Groups of rows are moved between sheets ("Open" to "Closed") before being archived to another workbook. In the last week, completing one of these moves (via vba code - copy, insert copied, delete original) has resulted in the workbook failing to open. Crashes Excel without any warning message. I can't even get it to open in safe mode.

The problem appeared to relate to one specific sheet so I set up some code to copy the data in the sheet and paste special into a new sheet, cell by cell, which took some time (14k rows). I deleted the suspect sheet and renamed the new version. No code is contained within the worksheet and all sheets are referred to by name.

It's no longer crashing on opening, but hangs (for over half an hour) when inserting a row and then eventually gives the error "System Call Failed", but has inserted the row. Obviously not usable in this state.

I'm after some general advice as to what I can do. Is there anything I can look at to try and pin down why it's suddenly become so unstable? Failing that, how would it be best to go about duplicating an entire workbook?

Any advice appreciated - I'm running out of ideas...!

Thanks, TC

Apologies for the delay.

You may well be onto something here. There shouldn't be any conditional formatting but there is and now I've removed it the insert / delete row is much, much quicker. I can also still open the file, bonus!

I'm working on a slimmed down version so I'm going to apply the same 'fix' to the main file and see if that sorts it out. I'll report back.

Yep - that sorted it! I've only been able to remove about 75% of the conditional formatting so far(the rest will need some code to replace the same functionality) but performance is quicker and it's stable again. I recreated all the issues that were causing it to fall over an it's handled it all fine.

I'll keep this in mind for the future!

Thank you - you saved me a lot of work in trying to replicate the file from scratch!