Problem with Excel memory


New Member
May 31, 2013

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?

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney


Well-known Member
Feb 4, 2012
Office Version
365, 2013, 2010
Windows, Web
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,


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...