Out of Memory on simple range copy!

  • Thread starter Thread starter stochastic
  • Start date Start date
S

stochastic

I have a large worksheet: 120 columns and 30000 rows
filled up with numeric values (plain values; no formulas,
no formatting of any kind). All I want to do is
programmatically copy this data onto another worksheet
in the same workbook.

It's puzzling and frustrating to see that several
alternative approaches have failed! In all cases, the
result is "Out of Memory" error of Excel, sometimes causing
Excel itself to crash.

Things that I tried (and didn't work):
(assume that I have already created named ranges called myrange
for source and targrange for the target. myrange is on sheet1 and
is filled up, and targrange is on sheet3 and is empty).

1. Sub try1()
Dim myarr As Variant
myarr = Range("myrange").Value
Range("targrange").Value = myarr
End Sub

2. Sub try2()
Dim nCols As Integer, nRows As Integer
Dim rSource As Range, rTarget As Range
Dim i As Integer
Set rSource = Range("myrange")
Set rTarget = Range("targrange")
nCols = rSource.Columns.Count
nRows = rSource.Rows.Count
For i = 1 To nCols
Application.StatusBar = i
rTarget.Columns(i).Value = rSource.Columns(i).Value
Next i
Application.StatusBar = False
End Sub

Tried on many different computers (all with Excel2000, but different
Windows versions). Also with 128k and 256k RAM.

This often works first time you run it; fails on second attempt.
On machine with less memory, first attempt itself fails.

Seems like a memory leak in Excel? Anyway, even if that be so,
I am looking for a workaround.

Any help will be hugely appreciated.

Thanks in advance.
Ajay Sathe
 
Ajay,

In addition to keepItcool's suggestion,
there are a couple of variations on your code that could be worth a try.
I prefer the second method...
'(1)------------------------------------------

Sub TransferValues()

Sheets(3).Range(ActiveWorkbook.Names("ThirdRng").RefersTo).Value = _
Sheets(1).Range(ActiveWorkbook.Names("FirstRng").RefersTo).Value

End Sub

'(2)--------------------------------------------

Sub TransferValuesAnotherWay()
Dim FirstRng As Range
Dim ThirdRng As Range

Set FirstRng = Sheets(1).Range("B5:D10")
Set ThirdRng = Sheets(3).Range("B5:D10")

ThirdRng.Value = FirstRng.Value

Set FirstRng = Nothing
Set ThirdRng = Nothing
End Sub
'--------------------------------------------
 
keepitcool,

I don't think I am using the clipboard in the approaches
I tried. Even a simple
targetrange.value = sourcerange.value
produces the same out of memory problem. (with respect
to the first example I gave, the difference here is that
no intermediate array is being used; it's a straight
assignment).

Still, I will try your suggestion, thanks.
- stochastic
 
James,

thanks for your suggestion, I am trying it out (not with
complete success yet). But conceptually, how will these
suggestions have a better chance at working compared to
what I tried?

TIA
- stochastic
 
Ajay,

Sometimes one thing works and another doesn't. Personally, I wouldn't
consider trying to move that much data and expect any consistency. I assume
you have calculation turned off while all this is going on.
It may be time to reconsider your approach...create your data twice, create
another workbook, use smaller data sets...

Charles Williams has a wealth of information on memory/calculation at:
http://www.decisionmodels.com/index.htm

Good luck,

Jim Cone
San Francisco, CA
**************************************
 
Back
Top