Out of memory-Converting Formulas to Values

[jomili]

This routine worked just fine on XLS workbooks, but not in XLSB workbooks. It gives me an "out of memory" error.

Please Login or Register  to view this content.

Since I have to do this daily, for about 20 sheets each for about 15 workbooks, what's the fastest way I could accomplish turning formulas to values?

[abousetta]

Convert the range values to an array then print the array back to the sheet

[jomili]

I'm not sure I understand how to do that.

[abousetta]

Here's an example. Open a new workbook and paste the code in a standard module and run.

Please Login or Register  to view this content.

[jomili]

Wow! I like that. With the test material the code below works, but I can't try it on my full data until the morning. But, if I'm having memory problems just converting the active range to values, won't the array cause the same problem?

Please Login or Register  to view this content.

Also, afterwards should I set arr = Nothing?, or is there no overhead with this method?

[abousetta]

I have seen arrays use up GB of RAM on my system and so I think you should be fine. If not, use smaller ranges.

From what I have read and seen in practice, if you are going to continue doing other activities in the subroutine then you may want to delete the array after its purpose is complete.

Please Login or Register  to view this content.

Chip Pearson has this to say about the matter "You can change the size of a dynamic array, but not the data type. When you Erase a dynamic array, the memory allocated to the array is released. You must ReDim the array in order to use it after it has been Erased."

abousetta