Problem with Excel memory

[KasperSørensen]

Hi,

Generel overview of Excel Sheet:
I use two Excel workbooks. One where I start my macros, creates my Pivot table and store all the data i need.
The second one, I use to extract data through SQL scripts to two worksheets and connect them through a lot of VLOOKUP functions and doing a lot of calculations. (I have approx 100.000 rows, and 35 columns with excel functions and my own created functions).
When all the data is extracted to the second workbook, I copy the two worksheets to the first workbook. Hereafter I have macros which create new worksheets and pivot tables.

The problem is that I often experience that Excel does not have enough memory. Every time I copy though a macro, i delete the clipboard afterwards, but it is not enough to make sure I have enough memory.

Can anybody tell if there is a good way of clearing Excel memory after the VBA codes have run? Can the problem be that while creating PIVOT tables, the catches is to heavy for Excel. Can I clear the catches for the Pivot?

 

[J.Ty.]

A random guess.

Excel must store the data necessary to perform undo, hence after you delete or alter a large amount of cells, it really keeps both the old and the new data. I did a simple check and this data is separate from the clipboard: after clearing the clipboard undo still works.

So I suggest that, after you finish your standard operations, try using Ctrl-Z in your spreadsheets. If in one of them it restores the previous content and this is a large change, it might be the culprit.

I do not know how to clear this cache, but you can obviously save and close the workbook and then reopen it. Note that actions of macros cannot be undone, hence it is likely to be a method to avoid wasting memory for undo information.

Best regards,

J.Ty.