code takes to long

[Scooter]

Why does this code take about 4-5 minutes to excute? I does 4 replacements
for each cell.

Range("C16:BJ17").Select
Selection.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

 

[Nigel]

Try running it by not selecting the range first?

With Range("C16:BJ17")
.Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With

 

[Per Jessen]

Hi

Try to skip the select statement:

Range("C16:BJ17").Replace What:="ABC", Replacement:="XYZ", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Regards,
Per

 

[Mike H]

Hi,

I would guess you have a lot of formula and each change is forcing a
recalculation so disable calculations and screenupdating

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'your code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

 

[Eric G]

It runs nearly instantly for me on some made up data, and properly does the
substitution. I'm running Office 2003

Eric

 

[Scooter]

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

 

[Scooter]

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

 

[Scooter]

That did not seem to have any affect. With 2003 it was fast and this
started with 2007.

 

[Scooter]

With 2003 it was fast and this started with 2007.