XL 2007 - grinding down to a halt after many rows - no objects,shapes or pagesetups

[IndigoDingo]

In XL 2007, I have a sheet with between 100 and 30000 rows at any one
time, across 40+ columns. Many have formulae in them.

The code takes a list of up to 10000 unique 10 digit numbers from
another reference sheet and puts them in an array. Each row of our
target data is checked against the array via 2 'for' loops.

A positive check results in some existing data in the target's columns
to be changed to alternative text.

A comment field up to 60 chars large receives a 5 alpha comment
appended to existing comments.

No objects or shapes are referenced. No pagesetup code is used or
performed prior to running the code (a recognised boo-boo for XL 2007)

Display update is suspended. Calculate is suspended.

However, if the target data has more than several thousand rows to
parse, the code grinds to a near halt. I can tell this because I have
a progress bar which starts very fast then slows right down and can
eventually even reach near zero progress. If I can break the run in
time, the sheet becomes molasses to negotiate. Other sheets it the
same workbook are fine and responsive.

So I check the blank cells for hidden data or formulas. None.
There is no new data at all, only replaced data. Update and Calculate
is restored. Formulas recalculate. Save the book and reopen in new XL
session. Still molasses. Book has doubled in size.

Thoughts:
Haven't been able to run in XL 03 to test if it's a 2007 problem.
There are OnChange events in the workbook, which I assume suspend
during the running of my code. In any case, why does the book still
get slulggish save/after reopen, etc.

Equipment: Core 2 Duo, 2 Gb RAM. XPSP3, XL 2007 SP2

tia,
I.D.

 

[Tim Williams]

If I understand your explanation correctly, I've had this exact problem
(repeatedly looping over rows).
You'll get much better performance if you first load up your row values from
the appropriate columns into
an array or maybe a dictionary object (use the cell value as the key and the
row number as the value:
if multiple instances of the same key then make an array of row numbers).

It seems like a lot of work compared to just looping through the rows each
time, but a dictionary
lookup is *much* faster.

Also, if you have onchange events which you don't need, then set
Application.EnableEvents = False
during your macro (don't forget to reset it when done).

Tim

 

[IndigoDingo]

Thank Tim,

I will give it a go. I found some nice data with about 20k+ rows to
try it on. It currently takes well over half an hour to crunch (well I
gave up after half an hour).
'Dictionary' is a new function to me. The testing data is already fed
into an array. But the target data is checked one row at a time (ie,
each row is checked against 5000+ numbers in an array). Will post the
results in a few days.

Thanks,

I.D.

 

[IndigoDingo]

D'oh!

I had a 'DoEvents' command in the loop. Commenting it out (disabling
my progress bar) reduced the runtime from over 30 minutes to under 30
seconds, without molasses in the resulting sheet.

Thanks anyway Tim. You got me interested in Dictionaries at least. : )

not-so-Indigo

 

[Tim Williams]

Next time...

Tim