Find and replace thousand of cell values
 

Replace works on multiple cells. Why would you loop through every cell and
use the replace explicitly on each individual cell when you could do it one
time on the entire range? The inner loop is definitely an immense time
waster.


--
Regards,
Tom Ogilvy

"AA2e72E" wrote in message
...
Too quick to send!

Activae is also time consuming.

Try and adapt this, if appropriate:

Sub FindReplace(ByVal what, ByVal replacement)
Application.ScreenUpdating = False
For Each Sheet In ActiveWorkbook.Worksheets
For Each Cell In Sheet.UsedRange
Cell.Replace what:=what, replacement:=replacement,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next
Next
Application.ScreenUpdating = True
End Sub

Sub aa()
FindReplace "Male", "THS IS A MALE"
End Sub

Find and replace thousand of cell values
 

Somehow, I assumed that since the menu option cannot be used (that would work on multiple cells as you say), there was room for refining the find/replace process i.e if the cell has a formula, don't replace etc. That's why I said, 'adapt, if appropriate'