View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Frank RoadRunner Frank RoadRunner is offline
external usenet poster
 
Posts: 3
Default Speed of Replace Range

I tried to solve the same problem as you and I found the code below is
working perfectly:

Sub REP()
Application.Calculation = xlCalculationManual
[A1:P1000].Select
Selection.Replace What:="Richard", Replacement:=[r1].Value, LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Application.Calculation = xlCalculationAutomatic

End Sub

I put 25 in the cell R1. Expression [r1] is the same as range("r1").

Monk pÃ*Å¡e:

I am trying to replace the values of a large range of cells with the value of
another cell (z5). However it takes a long time to replace the information
given the size of the range. It seems to be updating each cell individually.
Any suggestions would be appreciated. Thanks. Current code is:

Application.Calculation = xlCalculationManual
Range("A1:P1000").Select
Selection.Replace What:="Richard", Replacement:=Range("z5").Value,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Application.Calculation = xlCalculationAutomatic