Sheet suddently slow

[miami2k]

Dear All,

I'm working a quite big process with a userform with 8/10 buttons doing different things on a sheet.
The file il 1400 kb big and it used to be normally fast.
Some weeks ago all the processes suddently slowed down but I can't understand why and i don't know where to start to find out.
If I go though the macros with F8 all the steps are quite quick unless there are operations on the cells.
For example this command takes like 3 seconds: Cells(riga, 3) = "Annullato"This is ridiculus. Deleting a row take 10 seconds.
It is normal that a macro that does simple things of a sheet takes minutes when some weeks ago in 10 seconds was done.

Any clue to solve this is issue.

Can I copy the modules in a new sheet? Can I delete part of the sheet to see if slow?


Thank you very much.


miami2k

 

[vds1]

Hello,

Does your workbook consist of conditional formatting ?

 

[miami2k]

No all rules of conditional formatting deleted.

 

[vds1]

No all rules of conditional formatting deleted.

I would suggest you to copy and paste the code to the new workbook. Dont copy sheets, instead create one and verify your results.

 

[miami2k]

Ok, how do you suggest to copy the code? Export the modules? There are 15 Modules and 2 userforms.
Should I copy the formatting of the sheets from the old file to the new one?

Thank you.

 

[My Aswer Is This]

Try doing a save as. Give the workbook a new name.
Quit excel and then open up the newly named workbook and see what happens.

 

[miami2k]

Nothing, I save all the time with new names with the versions of the file but the new file is slow as much as the one Ive just closed.

 

[miami2k]

I've made a test. My file is made by 2000 lines. I've deleted half of them and performed tried to delete a like. It takes one tenth of the time it took before but it does not make sense.
Delete a line in a 10 lines sheet is the same operation of deleting it in a 2000 lines sheet. The operation does not affect the other lines.
Any idea how to improve the process?

 

[rlv01]

Over time, Excel can build up a lot of "excess baggage". If you are still using a 32-bit* version of Excel, try using Rob Bovey's excellent and free code cleaner utility. This is has been one of my go-to utilities for years.

http://www.appspro.com/Utilities/CodeCleaner.htm

*Does not matter if Windows is 64bit. Sadly, Code Cleaner is not compatible with the 64bit version of Office

 

[miami2k]

Thank you it improves it a bit but this is not the solution.

I'm afraid the file became simply too bit. Performing simple operations such as deleting a row even without running any code takes 2/3 seconds.