Name

range.Replace(What, Replacement, [LookAt]), [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat])

Synopsis

Replaces text within the cells in the specified range.

Argument

Settings

What

The string to search for.

Replacement

The replacement string.

LookAt

xlPart (default) searches within the cell contents; xlWhole searches whole cells.

SearcbOrder

xlByRows (default) searches one row at a time; xlByColumns searches one column at a time.

MatchCase

False (default) ignores case; True performs a case-sensitive search.

MatchByte

If double-byte language support is enabled:

  • True matches double-byte characters.

  • False matches double-byte characters to their single-byte equivalents.

SearchFormat

The search format.

ReplaceFormat

The replace format.

The following code replaces all occurrences of the string "Ichiro" in the specified range with the string "Suzuki":

Dim r As Range
 
Set r = ActiveSheet.Range("A1:A6")
r.Replace "Ichiro", "Suzuki"

Get Programming Excel with VBA and .NET now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.