Good night people,
I have a task to do which I use a find and replace code that works by selection:
Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
Next
Application.ScreenUpdating = True
End Sub
The code is great and does the job, but it can take hours to process when I'm working on a 100K rows with cells to be found and replaced by 100k different matches from another worksheet.
Could anyone suggest me something to improve the performance?
PS: I'm not exactly sure how it affects the coding, but the column contains blank cells between the values. Maybe telling it to auto skip it would save it some time? (sorry if it sounds stupid, I'm kind of new with excel)
Bookmarks