error '7' in VBA with Excel 2007

Question

I'm developing a Macro for VBA in Excel 2007

and there's a section which fills a ListBox with 11 columns. Sometimes when I call this method for refilling the info appears a message which says:

Se ha producido el error '7' en tiempo de ejecución: Memoria insuficiente

in english is something like:

There was an error '7' at Runtime: Insufficient memory

and the code points at this line of code:

vList = ws.Range("A2", ws.Range("A2").End(xlDown).End(xlToRight))

I assured to release memory by setting all the objects used on this Function to Nothing

this is my full code:

Function llenarDatosTabla()

    Dim vList As Variant
    Dim ws As Worksheet: Set ws = Worksheets("PRODXSISTDATA")


    If (IsEmpty(ws.Range("A2").Value) = False) Then
        vList = ws.Range("A2", ws.Range("A2").End(xlDown).End(xlToRight))
        Me.ListBox1.List = vList
    End If

    Set vList = Nothing
    Set ws = Nothing
End Function

Your assignment to `vList = ws.Range(...` statement is the wrong way of determining the last used cell in a range. The most likely reason for this error is that the resultant range is too big (because it is not what you *think* it should be). — David Zemens, Dec 02 '14 at 16:43
I would suggest you to read this answer: [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Dmitry Pavliv, Dec 02 '14 at 16:44
I get an 'Out of Memory' exception with an array size like: `vList = Range("A1").Resize(1048576, 32)`. I get similar error at `.Resize(2001, 16384)` so there appears to be some limit of around 33 million cell values that can store in an array. This is tested on an empty worksheet in Excel 2010. — David Zemens, Dec 02 '14 at 16:56

score 0 · Answer 1 · answered Dec 02 '14 at 19:34

Defines LastRow and LastCol seperately so you can ensure you are grabbing the correct range.

Dim LastRow As Long, LastCol As Long
'Dim your other stuff

If IsEmpty(ws.Range("A2").Value) = False) Then
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    'Add this if still an issue to confirm correct LastRow and LastCol:
    'MsgBox "LastRow= " & LastRow & " LastCol= " & LastCol 
    vList = ws.Range(Cells(2, 1), Cells(LastRow, LastCol))
'etc etc etc

error '7' in VBA with Excel 2007

1 Answers1