Optimizing a macro for speed- find and replace
 

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......

--
trickdo
-----------------------------------------------------------------------
trickdos's Profile: http://www.excelforum.com/member.php...nfo&userid=940
View this thread: http://www.excelforum.com/showthread.php?threadid=27756

Optimizing a macro for speed- find and replace
 

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

--
shie
-----------------------------------------------------------------------
shieb's Profile: http://www.excelforum.com/member.php...fo&userid=1640
View this thread: http://www.excelforum.com/showthread.php?threadid=27756

Optimizing a macro for speed- find and replace
 

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

"shieb" wrote in message
...

I know you already have a solution, but I was bored and curious. It
appears using an array is about 70% faster than the Range.Replace
method. Here's an example using 1 sheet, a named range, 25k rows x 10
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 down
in your orginal code. So, the Application.EnableEvents = False in
prior posts probably helped a lot.

Regards,
Steve Hieb


--
shieb
------------------------------------------------------------------------
shieb's Profile:
http://www.excelforum.com/member.php...o&userid=16404
View this thread: http://www.excelforum.com/showthread...hreadid=277560