Quickly Update Range

[Guest]

What's the quickest, most efficient way to do something to every cell in a
range? I've got a range of cells, and I want to do one of the following
depending on user params:

1) replace every value with an apostrophe and the value (coercing it to a
text data type for Access import)
2) replace every value with the Val() of that value (replacing blank cells
with 0, again for Access import)

I'm conversant with the "For Each C In objR.Cells" loop, but I've got
20,000+ rows and this loop is taking too long.

Any other suggestions that would accomplish the same thing would be greatly
apperciated.

 

[Guest]

Hi,
Probably the quickest way is to read the range into a variant
array, perform your calculations and transfer the variant array back to the
range.

A very simple emulation of your requirements for 30,000 rows and 50 columns
took less than 10 secs.

Is this performance acceptable?

HTH