Performance upgrade of current calculation method

[Villalobos]

Hello,

I struggle with big data and I do not see the light the end of tunnel. In my sample file I would like to calculate the monthly inventory value by distinct company and material the code is working fine just the problem is that the total calculation time is: 20 sec.

The original data source set has > 50.000 rows and need 650 sec on my PC.

Do you have any idea how could be upgrade the performance?

Additional info: I have to keep the current data structure on sheet Evaluation.


Thanks in advance the reply!

 

[Nebu]

Hi:

Why do you even need a macro for this, as per my understanding a simple pivot will do the trick for you.

Thanks

 

[Villalobos]

Hi Nebu,

Yes, you are right. The pivot table would be a good solution. Just I have to keep the current data layout and to add pivot table is not allowed.

 

[Nebu]

Hi:

I am assuming the current layout of your data is as in the tab InventoryMonthly

please find the attached , I have created a pivot without changing the layout of your data.

Thanks

 

[NARAYANK991]

Hi ,

Can you test the code in this file with your data of 50,000 rows ?

What I see is that if the output range is manually cleared , then the time is less ; can you verify this ?

I included the range clear in the code , and the time is 20 seconds ; when I clear the range manually , the time is less.

Narayan

 

[Villalobos]

Hi Narayan,

Thank you the help!