help fix code from out of memory error

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
Code:
Sub ArrayFillRange()
'   Fill a range by transferring an array
    Dim CellsDown As Long, CellsAcross As Long
    Dim i As Long, j As Long
    Dim StartTime As Double
    Dim TempArray() As Double
    Dim TheRange As Range
    Dim CurrVal As Double

'   Change these values
    CellsDown = 1048576
    CellsAcross = 16384

    Cells.Clear
'   Record starting time
    StartTime = Timer

'   Redimension temporary array
    ReDim TempArray(1 To CellsDown, 1 To CellsAcross)

'   Set worksheet range
    Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))

'   Fill the temporary array
    CurrVal = 4.41028223935346E-02
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For i = 1 To CellsDown
        For j = 1 To CellsAcross
            TempArray(i, j) = CurrVal
            CurrVal = CurrVal + 1
            If CurrVal = 4.47028223935344E-02 Then
            Exit Sub
            Else
            End If
        Next j
    Next i

'   Transfer temporary array to worksheet
    TheRange.Value = TempArray

'   Display elapsed time
    dApplication.ScreenUpdating = True
    Application.EnableEvents = True
    MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Why are you using this

Code:
 CellsDown = 1048576
    CellsAcross = 16384


Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))

When you could easily use either usedrange or lastused row and last used column
Code:
activesheet.usedrange
 
Upvote 0
@mperrah
currentregion doesn't include ALL data in the UsedRange, if the current region is bounded by blank rows or columns !!
 
Upvote 0
I found this code online while searching for how to fill a sheet with values. The way I understood this, is that this code is suppose to make a temporary array to put your values into it and then it take those temporary values and places them into the worksheet. The cellsDown, CellsAcross is, as I am assuming (I am no programmer), the area on the sheet that you want to use.
 
Upvote 0
I understand that, but you are Redimming an array of 17 Billion cells....do you really need to do that.
These values need to change to a number relative to your needs
Code:
 Change these values
    CellsDown = 1048576 'change to number of required rows, not the entire sheet

    CellsAcross = 16384 'change to the required amount of columns, not every column
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top