replace all the cells in sheet that include spsific string-via mac
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
"Miri" wrote:
i need to replace all the cells in spesific sheet that include spesific
string with other.
i did the following code:(but 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
--
Miri Tz.
|