Out of Memory error - Excel VBA

[Fenrirshowl]

I've been using the same spreadsheet for several months and it's grown to about 24Mb. Today, it fails to open - I just get a "not responding" from task manager no matter how long I leave it.

There is one particular sheet I need access to and I am at the point where I don't overly need the backing calculations, just the values. [The data (23,500 rows by 30 cols) would have been better in Access but the powers that be would only have it in Excel.] I am trying to recreate the values in a new spreadsheet by linking them to the same cell location on the original. In order to get rid of the links, I copy and paste the values. Every 1000 cells or so I save the document.

Unfortunately I am running out of Memory on a regular basis, so I am: breaking the code, saving the spreadsheet, closing excel, starting it up again with a clear memory and rerunning the macro I've set up. I've got bored of this and I don't want to have to babysit my machine when I could be doing other things.

I am hoping someone can tell me how to clear the memory without having to shut down excel so I can set up some error handling.

Also, if anyone has an idea about how many formulas I can add to link the sheets at one time I would appreciate a pointer(currently I am adding them one at a time in a hope to minimise system resources).

Here's the code I am using:

Sub enter_linked_data()
Application.ScreenUpdating = False
For y = 2 To 31
        x = 1
        While x < 23500
            Cells(x, y).Select
            rng = ActiveCell.Address
            Range(rng) = "='C:\My Documents\[MyWorkbook.xls]Sheet1'!" & rng
            ActiveCell.Copy
            Range(rng).PasteSpecial xlPasteValues
            If Range(rng) = 0 Then Range(rng).ClearContents
            If Int(x / 1000) = x / 1000 Then ActiveWorkbook.Save
            x = x + 1
        Wend
Next y
Application.ScreenUpdating = True
End Sub

All help gratefully received.

Fen

 

[xlbo]

try this:

Sub enter_linked_data()
Application.ScreenUpdating = False
For y = 2 To 31
x = 1
While x < 23500
Cells(x, y).Select
rng = ActiveCell.Address
Range(rng) = "='C:\My Documents\[MyWorkbook.xls]Sheet1'!" & rng
ActiveCell.Copy
Range(rng).PasteSpecial xlPasteValues
Application.cutcopymode = false
If Range(rng) = 0 Then Range(rng).ClearContents
If Int(x / 1000) = x / 1000 Then ActiveWorkbook.Save
x = x + 1
Wend
Next y
Application.ScreenUpdating = True
End Sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fenrirshowl]

Thanks for the reply Geoff but I tried it & it still died on me (same error).

I've gone for a dirty option of running the macro from another workbook with error handling code to do the save / shut down / re-open / re-start macro approach:

Sub run_clone()
Workbooks("clone.xls").Activate
Call create_clone
End Sub

Sub create_clone()
On Error GoTo shut_down
10 Application.ScreenUpdating = False
x1 = ActiveCell.Row
c1 = ActiveCell.Column
x = x1
For y = c1 To 31
        If x = 0 Then x = 1
        While x < 23500
            Cells(x, y).Select
            rng = ActiveCell.Address
            Range(rng) = "='C:\Documents and Settings\lindahr1\My Documents\excel_files\rank\Jan07\[NICO-JLT match.xls]NICO data'!" & rng
            ActiveCell.Copy
            Range(rng).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            If Range(rng) = 0 Then Range(rng).ClearContents
            If Int(x / 1000) = x / 1000 Then ActiveWorkbook.Save
            x = x + 1
        Wend
        x = 0
Next y
Application.ScreenUpdating = True

shut_down:
    Range(rng).ClearContents
    ActiveWorkbook.Save
    filenm = ActiveWorkbook.FullName
    ActiveWorkbook.Close
    Workbooks.Open filenm
    MsgBox "check"
    GoTo 10

End Sub

seems to be working!

 

[GlennUK]

Whenever I've done this I've done array-linking, linking hundreds if not thousands of cells per formula ( there used to be a max of 6553 cells per array ). This should save on system resources being eaten up.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

Thanks Glenn, I'll look into it and see what happens.