Opening a excel file with code takes more than 5 minutes

[RAJESH SHAH]

Hello Experts,
I have this file with 4 modules to derive at the result. The problem is opening the file. It takes more than 5 minutes to open. In the status bar it is showing calculating and extracting 1%, 2% and so on and it is very slow. How do I overcome this.? Please share your suggestions what to do and what could be wrong.

[JohnTopley]

I suspect the reason is the number and type of formulae (array entry?) being used. If your last post, you said you had difficulty uploading a 25 row sample so there is obviously something seriously wrong wirh your workbook.

From your previous posts you have continuously added/amended the code where you have simple used code to insert formulae [when using native VBA would be better]: it is probable/highly likely this has introduced many inefficiences.

Without a workbook, it is not possible to identify the problem arreas.

[RAJESH SHAH]

My first stage of the project is completed successfully. It takes less than 3 minutes for the code to give me the result. Opening the file takes much more time than the code.
As I said my data is very huge up to 1 lakh rows plus. Without the whole data you will not be able to find the reason for the problem. If it is ok I can share a link in private for you to chack. Please let me know.

[JohnTopley]

Provide details (by sheet) of number of rows/columns, formulae used e.g SUMPRODUCT. It is not necessary to supply a 10.000+ row sampe as it should be possible to identify liklely problem formula.

Opening the book is obviously resulting in the workbook doing a lot of re-calculating.

https://www.webnots.com/fix-slow-mic...et-processing/

https://www.youtube.com/watch?v=nn-KGloaVsA

and other examples of such advice are on the web.

[RAJESH SHAH]

I don't know how it will help you to understand but here goes..

Sheet 1 data is as received from the client. Rows 1,27,955 and columns 68
Sheet 2,3,4,5, are the data extracted from sheet1 -
sheet 2 89,855 rows and columns 68
sheet 3 20527 rows an columns 26
sheet 4 7546 rows and columns 68
sheet 5 362 rows and columns 14 but with some unwanted additional rows still to be recrified

What if I share the codes will it be possible to check and see what is the problem ?

[JohnTopley]

Although large, 128000 rows is not exceptional but performance does depend on what formulas are performed.

The best way is post a sample file with a few hundred rows BUT include all sheets, together with the VBA code.

[RAJESH SHAH]

I am facing another problem. Please let me correct both the issues first and if possible to share all the sheets will do so with less than 100 rows.

[JohnTopley]

Thank you.

[Pete_UK]

I supplied you with a formula for conditional formatting, and you went on to apply this to a complete column, and complained then that it was slow. That may be one of the reasons that the file is slow to open, so you should minimise the range over which the CF applies.

Hope this helps.

Pete

[RAJESH SHAH]

I supplied you with a formula for conditional formatting, and you went on to apply this to a complete column, and complained then that it was slow. That may be one of the reasons that the file is slow to open, so you should minimise the range over which the CF applies.

Hope this helps.

Pete

I think you are talking about some other project. There is no conditional formatting in this workbook.

[RAJESH SHAH]

After closing and opening the file multiple times, looks like the problem is resolved. The calculation & threading message has vanished. Will check again tomoorow, If it is working fine I will close the post tomorrow.