VBA on Excel "Out of Memory" error -

i'm on excel 2010, on admittedly large sheet (400k rows x 20 columns).

my code aims to:

• load entire sheet array
• examine every row criteria
• rows qualify copied array
• finally return second array sheet
• the second array end being 90% of original

i wrote definition of 2 variable arrays variants , tried initialize them copying sheet's content twice.

first copy works, second 1 hit error of "out of memory".

any ideas if there's workaround? or limitation of vba/ excel.

is there way not pre-define / initialize destination array, , instead, let "grow" every successful qualification of criteria? (on scale of magnitude).

sub copypending() dim lastrow long dim lastcol integer dim allrange() variant dim copyrange() variant dim long dim x long dim z long  lastcol = 21 lastrow = activesheet.usedrange.rows.count  allrange = range(cells(2, 1), cells(lastrow, lastcol)).value copyrange = range(cells(2, 1), cells(lastrow, lastcol)).value ''' error trigger  = 1 x = 1 z = 1  = lbound(allrange) ubound(allrange) - 1   if allrange(i, 7) = "testcriteria"     z = 1 lastcol       copyrange(x, z) = allrange(i, z)     next z     x = x + 1   end if next  sheets(2)   .range(.cells(2, 1), .cells(x, lastcol)).value = copyrange end  end sub 

as comments on post indicate, error comes shortage of working memory.

each variant type variable consumes 16 bytes, reason code require vast amount of memory. 1 way solve problem increase physical memory on computer.

other solution filtering data amount of rows.

sub processrows()     dim originaldata() variant     dim maxrow long, currentrow long, incrementrow      maxrow = activesheet.usedrange.rows.count     currentrow =1     incrementrow=5000      while currentrow < maxrow         set originaldata = range(.cells(currentrow,1),.cells(currentrow+incrementrow-1,20)          process filter data          currentrow = currentrow +incrementrow     wend end sub  

of course can go row row approach, assume use array variable speed code, not recommend use row row approach.