replace all the cells in sheet that include spsific string-via mac

[Miri]

i need to replace all the cells in spesific sheet that include spesific
string with other.
i did the following codebut since there are lot of cells in sheet , it is
very very havy. is there a better way? )

Workbooks(Activebook).Sheets("mySheet").Select
Cells.Select

mystr = "xxx"
For Each cell In Selection
If (InStr(cell.Value, mystr) = 1) Then
cell.Replace What:=mystr, Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False,
ReplaceFormat:=False

End If
Next cell

 

[Mike H]

Miri,

You could limit your code to the used range instead of the entire worksheet.
Try this and note that I've added a space either side of your string. The
reason for this is if your string was 'add' it would remove the 'add' from
'Haddock'.

Sub stantial()
Sheets("Sheet1").UsedRange.Select
mystr = " xxxx "
For Each cell In Selection
If InStr(1, cell.Value, mystr, 1) > 0 Then
cell.Replace What:=mystr, Replacement:=""
End If
Next cell
End Sub

Mike