Optimizing a macro for speed- find and replace

[trickdos]

PaulD,

I used your macro instead of mine, and it sped up to about 3 second
per sheet. This was very useful because my macro got hung up somewher
and took forever. Thanks again. Updated over a half-millio
cells......

 

[shieb]

I know you already have a solution, but I was bored and curious. I
appears using an array is about 70% faster than the Range.Replac
method. Here's an example using 1 sheet, a named range, 25k rows x 1
columns:

Sub ArrayTest()
Dim A As Variant
Dim R As Long
Dim C As Long
A = Sheet1.Range("DataSet").Value
For R = 1 To 25000
For C = 1 To 10
If A(R, C) = " " Then A(R, C) = ""
Next C
Next R
Sheet1.Range("DataSet").Value = A
Set A = Nothing
End Sub

This took 1.109 seconds compared to 4.110 using a .Replace method.

FYI, my guess is you have worksheet level events causing the slow dow
in your orginal code. So, the Application.EnableEvents = False i
prior posts probably helped a lot.

Regards,
Steve Hie

 

[PaulD]

you got way too much free time
Interesting to know though, every second counts...
Paul D