Out of Memory on simple range copy!
 

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

Out of Memory on simple range copy!
 

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").R efersTo).Value = _
Sheets(1).Range(ActiveWorkbook.Names("FirstRng").R efersTo).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
'--------------------------------------------

"stochastic" wrote in message
om...
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

Out of Memory on simple range copy!
 

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

keepitcool wrote in message . ..
Ajay,

When copying large ranges, remember to:
turn off the Office CLipboard
use application.cutcopymode=false after each paste to clear clipboard.

all of the above considered..
have you had a look at VBA help on Copy method of the range object?

you'd have noticed it has a Destination argument.

[sheet1!a1:z30000].copy [sheet2!b11:aa30010]
if the syntax that requires least memory.

And some of your stations have only 128MB, which in these days (and with
this amount of data) just aint enough.

Also for speed considerations consider to copy the entire sheet object
rather than the RANGE (which is a whole bunch of individual cells)

It will be lots faster and require less resources.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

Out of Memory on simple range copy!
 

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

"James Cone" wrote in message ...
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").R efersTo).Value = _
Sheets(1).Range(ActiveWorkbook.Names("FirstRng").R efersTo).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
'--------------------------------------------