Is there a way to purge memory?

[Johnny C]

I've got a workbook that's processing a lot of data, about 850K rows. It's using about half a Gb. The wbk itself loads a text file and splits it into sheets in a new workbook which is saved and closed, then all the data read in from the text file is deleted, cell A1 selected and the host workbook saved.

The problem is this has to run on a number of files. After a few, Excel crashes due to lack of memory. I don't want the user to have to shut Excel down every time. When I check the memory wit performance monitor, each time it's run and I close all the workbooks down, it's using an additional 130M memory which isn't being freed up even though there are no workbooks open.

The VBA is clearing the clipboard every time the code is run with application.cutcopymode=false

Is there any thing else I can do to force memory garbage collection?

(PS Can I turn ff this atomatic spellcheckr and utosave , it losing half the letters I typ?)

 

[CharlesChuckieCharles]

use an almost empty work book to load the host ( its job it to load the host)

Then after the processing of the text files
close the host workbook without saving

Then reload it for the next text file

 

[hiker95]

Johnny C,

To start off, can we see your macro code?

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Johnny C]

I could, but I'm not sure you'll want to see it - there's 450 lines of it !

It's saved as a small workbook. just a couple of one page sheets. The VBA loads upto 850,000 lines from a text file, some of the lines need splitting into 2 so I can't copy and paste. It then copies a bunch of formulae down (mostly text splitting functions and OFFSETs. It then whizzes down splitting the data into separate chunks and copying them to new sheets in a new workbook.

Then it deletes all the data that was copied in and the corresponding formulae, goes up to A1 and saves itself to reset the last used row.

The workbook including VBA is only 67K. The new workbook created is about 8Mb for 250K lines, so won't be that big and that's pure text, no formulae or formatting.

The Windows garbage collector works when I close Excel, but Excel itself doesn't seem to have any background garbage collection, I just wondered if there's a mso type call to start it off.

I suspect it's the formulae I copied down have created a ginormous calculation tree. I might play around with some of the calculation tree type VBA functions tomorrow, like doing ctrl-alt-shift-F9 to force a rebuild of the calculation tree and see if that free memory up.