help fix code from out of memory error

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: help fix code from out of memory error

  1. #1
    Board Regular
    Join Date
    Oct 2013
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post help fix code from out of memory error

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help fix code from out of memory error

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Mar 2005
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help fix code from out of memory error

    or currentRegion

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help fix code from out of memory error

    @mperrah
    currentregion doesn't include ALL data in the UsedRange, if the current region is bounded by blank rows or columns !!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    Oct 2013
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help fix code from out of memory error

    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.

  6. #6
    Board Regular
    Join Date
    Oct 2013
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help fix code from out of memory error

    The error is highlight the redim temporary array line.

  7. #7
    Board Regular
    Join Date
    Oct 2013
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help fix code from out of memory error


  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help fix code from out of memory error

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #9
    Board Regular
    Join Date
    Oct 2013
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help fix code from out of memory error

    I realized this late last night. Thanks for your help.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com