Overcome VBA array size limitation

  • Thread starter Thread starter Francine Kubaka
  • Start date Start date
F

Francine Kubaka

Hello,

I am desperate. I have a dataset which contains 15,000 rows by 25 columns
(and I don't want to switch to Access at this point). I desperately need to
perform several simple and complex calculations on this dataset and would
rather do it by VBA arrays as opposed to ranges.

For example I'd rather loop ARRAY(3, i) = ARRAY(1, i) + ARRAY(2, i) and
then pass the resulting array to the worksheet than do a thousandfold
cell.offset loop.

Help please?

K
 
Francine,

There is no need to loop to do the calculation you want.
The following macro runs in an instant, by working on the whole range at
once.

================================
Sub Calculate5000RowsInstantly()
With Range("C2:C5001")
.FormulaR1C1 = "=RC[-2]+RC[-1]"
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub
===========================================

If there are other reasons why this method isn't suitable, please reply.

Regards,

JohnI
 
I guess I shoud just try it...
I agree that it should be much faster than looping through individual cells.
One problem though, - several columns in "precedents" are hours and
weeknumbers, thus the "dependants" should switch accordingly, i.e.
IF(R[-1]C[-1]<>24, R[-1]C, R[-1]C+1) . I guess I should just incorporate the
switch in the range formula. And thirdly, only a few of my formulas are like
this, most of them are either GETPIVODATAs or LOOKUPs or both. Still, I
think your suggestion should speed the process up considerably.

Thanks,

Francine


JohnI in Brisbane said:
Francine,

There is no need to loop to do the calculation you want.
The following macro runs in an instant, by working on the whole range at
once.

================================
Sub Calculate5000RowsInstantly()
With Range("C2:C5001")
.FormulaR1C1 = "=RC[-2]+RC[-1]"
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub
===========================================

If there are other reasons why this method isn't suitable, please reply.

Regards,

JohnI

Francine Kubaka said:
Hello,

I am desperate. I have a dataset which contains 15,000 rows by 25 columns
(and I don't want to switch to Access at this point). I desperately
Click to expand...
need
to
perform several simple and complex calculations on this dataset and would
rather do it by VBA arrays as opposed to ranges.

For example I'd rather loop ARRAY(3, i) = ARRAY(1, i) + ARRAY(2, i) and
then pass the resulting array to the worksheet than do a thousandfold
cell.offset loop.

Help please?

K
Click to expand...
Click to expand...
 
Back
Top