Copying array formula to last row using VBA.

Thanks again AlphaFrog, it works great.

Just one last thing, I would like to live the calculated value in the cell rather than the formula, I tried:

Please Login or Register  to view this content.

But it only copies the formula onto the first row

Sorry for the delay in replying, the above code works fine as long as the calculations are set to automatic (which would make sense). However in my case, it seems that many users set the calculation option to manual (due to the high number of formulas the spreadsheet contains).

If the formula to copy down to the last row is a 'basic' formula, I use the code below to paste the resulting value, it works fine even if calculations are set to manual:

Please Login or Register  to view this content.

The above code works for 'basic' formulas, even with calculations set to manual, but it does not work for array formulas. Is there a way to copy array formulas all the way down to the last row and leaving the resulting value only, when calculations are set to manual?

I tried adding the following lines to the previous code provided by AlphaFrog (though I would like to avoid enabling automatic calculations, if possible at all), but did not help:

Please Login or Register  to view this content.

Hi AlphaFrog, this is perfect, thanks again!

Ant