FASTER USE OF THE REPLACE() FUNCTION !!!!!

[jay dean]

Hi -

I am using the Replace() function for a very large range. It works okay
but wait time is quite long. Is there a more elegant approach to the
below?

For Each rng In Range("B3:B65500")
If rng.Value <> "" Then
rng.Value = Replace(rng.Value, "-", ",")
End If
Next rng


Any help would be appreciated! Thanks

Jay

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein]

Try it this way...

Range("B3:B65500").Replace "-", ","

 

[Rick Rothstein]

I just wanted to mention as a follow up that I'm using the Replace method of
a range rather than VB's Replace function... it will be much faster as you
don't have to loop the cells individually.

 

[JLGWhiz]

Would this not work:

For Each rng In Range("B3:B65500")
rng.Replace "-", ","
Next rng

 

[Mike H]

Hi,

First let's make sure were not doing too many rows by making the range
dynamic by finding the last row. Then stop the screen updating and the
worksheet calculating. Doing this on my pc reduced the runtime from 11
seconds to less than 4

Sub BlankDays()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Rng In Range("B3:B" & LastRow)
If Rng.Value <> "" Then
Rng.Value = Replace(Rng.Value, "-", ",")
End If
Next Rng
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

You don't have to loop the range... you can apply the range's Replace method
to the entire range all at once...

Range("B3:B65500").Replace "-", ","

 

[tompl]

Good answer. I don't see what the if statement accomplishes. I the cell is
empty no "-" would be found anyway.

Tom

 

[Mike H]

Yes,

Excellent point about the IF I never noticed that. I also meant to add that
with a worksheet full of formula the increase in performance will be dramatic.

I filled column c with a simple formula =d1/e1 and re-ran the test and got
4.2 seconds runtime with calculation/screenupdating disabled and 200 seconds
with them enabled.

Surprisingly, well to me it was, with the IF statment removed the run time
only went down to 3.9 seconds but the point about removing it is still valid

Sub BlankDays()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Rng In Range("B3:B" & LastRow)

Rng.Value = Replace(Rng.Value, "-", ",")

Next Rng
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

First off, I would highly suggest the method I posted be used as it will be
much, much faster than looping the cells. However, with that said, if one
were going to use the posted loop, it should be more efficient to test the
cell and only apply the Replace function call if a dash is found... the If
test will execute much faster than the Replace function call will... Replace
is not one of VB's faster functions and, for cells with no dash in them, it
will still spend some time deciding that it has nothing to do.

 

[Mike H]

Rick,

I'm baffled --- help.

Using your suggestion and with the code below i get a runtime of ~2.4 seconds.
Un-Comment the calculation and screenupdating lines to disable them and the
runtime goes UP to ~3.1 seconds. Why is that happening, am i making an
elementary error?

Sub BlankDays()
starttime = Timer
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
Range("B3:B65500").Replace "-", ","
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAutomatic
MsgBox "Runtime " & Timer - starttime
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[jay dean]

Thanks, Rick, JLG, and Mike.
I adopted Rick's approach as it eliminates looping and is fastest!

Thanks, again
Jay

*** Sent via Developersdex http://www.developersdex.com ***