get result through VBA instead of function

[Afarag]

Hello there,

Please, I need help, as I have a worksheet with a huge range of data reach up to 700k rows and more and some columns which have a function. due to the long range, I can't apply the function, as the worksheet take a long time to load, or it crashes. So I search for an effective way to find a final solution to save more time in waiting for the loading or fixing the crash.how Can I get the result through VBA model?

thanks in advance,

 

[Marc L]

Hi !

Can you at least clearly and technicaly explain your need ?!

When data are huge, better is to forget Excel and use Access,
more reliable, no crash, …
​

 

[Afarag]

Hi Marc,

I Know that VBA could replace the formulas function without high loading if the range is huge. I need to get the result indexed in columns [N] till [AA] via code instead of formulas.

for Access, is there is availability to create formulas like Excel

thannks

 

[Marc L]

Once you have your formula results, you can easily replace them
by their values just via a copy and paste only values, no need a code …

Access is a data manager, so you have to create tables
and compute calculated area …

Excel 32 bits version may often crash with huge data …
You can raise its memory limit using 64 bits version
but with losing some activeX components used in Userform or code.
Some VBA code must be arranged under 64 bits version …
​

 

[Afarag]

The problem centered in applying the formula for that huge range, I use that sheet as a template, I use it daily, each day I remove the old data then add the new day and surprisingly 700k row is for one day, so I need to keep the range with active formula as I can't Copy / PasteSpecial, when add the new day then drag down the formulas to fit the data range, it highly loading or crash

 

[Marc L]

So if you start each day with a blank worksheet,
you can create a code to apply formula for a column,
replace formula by result values and do the same for next columns.

Use Range properties : Formula and Value …

See this thread how a formula is applied to a column
and how this formula is replaced by result …
​