excel - REALLY SLOW Loop -

excel - REALLY SLOW Loop -

can give me insight why takes long run? i'm running winxp on parallels, 16gb macbook pro (4 gigs allocated vm). spreadsheet (created client) absolute nightmare - 38 sheets total of ridiculously complicated formulae, , multi-step overly complex algorithms create rube goldberg quite jealous. still, simple routine takes 30 minutes run.

sub onelist() 'application.screenupdating = false ncols = range("scores").columns.count nrows = range("sc_id").rows.count 'msgbox nrows, ncols redim preserve scores(1 nrows, 1 ncols) = 2 nrows j = 1 ncols scores(i, j) = application.index(range("scores"), i, j) ' debug.print i, j ' debug.print scores(i, j) ' sheet36.range("a1:d197").cells(i - 1, j).value = scores(i, j) next j next sheet36.range("a1:d197").clear = 1 nrows b = 1 ncols sheet36.range("a1:d197").cells(a, b).value = scores(a, b) next b next end sub

thanks in advance insights.

you have 2 loops, , within loops alter values on sheet. every time alter value formulas calculate on front end end. consider stopping formula auto-calculation

application.calculation = xlcalculationmanual

and @ end of script turn them on

application.calculation = xlcalculationautomatic

while off can forcefulness calculation run

application.calculate

updated:

it looks of unnecessary well. iterating through range, assigning each , every cell 2 dimensional array, iterating through array , spitting values different range.

why not just:

sub onelist() sheet36.range("a1:d197").value2 = range("scores").value2 end sub

excel vba multidimensional-array