VBA セルを置換する (Range.Replace)

はじめに

Excel VBA マクロでセルを置換する方法を紹介します。

Range.Replace または Cells.Replace メソッドから、セルを置換できます。

Application.FindFormat プロパティから、書式を検索対象にできます。

Application.ReplaceFormat メソッドから、書式を置換できます。

Replace 関数で文字列を置換するには「文字列を置換する」をご覧ください。
セルを範囲指定するすべての方法は「セルを範囲指定して取得する」をご覧ください。
  • 目次
    • Replace メソッド
    • セルを置換する
    • 書式を検索条件にする
    • 書式を置換する
    • 別シートのセルを置換する

Replace メソッド

Replace(What, Replacement)

Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

引数「What」検索する文字列やワイルドカードを指定します。
引数「Replacement」置換する文字列を指定します。
引数「LookAt」省略できます。完全に一致するセルを検索するか指定します。
  • xlPart:一部分が一致するセルの検索
  • xlWhole:完全に一致するセルを検索
引数「SearchOrder」省略できます。どの方向に検索するか指定します。
  • xlByRows:行方向
  • xlByColumns:列方向
引数「MatchCase」省略できます。大文字と小文字を区別するか指定します。
  • True:区別する
  • False:区別しない
引数「MatchByte」省略できます。半角と全角を区別するか指定します。
  • True:区別する
  • False:区別しない
引数「SearchFormat」省略できます。書式を検索条件にするか指定します。
  • True:検索条件にする
  • False:検索条件にしない
引数「ReplaceFormat」省略できます。書式を置換するか指定します。
  • True:置換する
  • False:置換しない
戻り値の型ブール型 (Boolean)

置換するしないに関わらず True を返します。

このメソッドは Excel の置換ダイアログと連動しています。引数を指定すると置換ダイアログのオプションが変更されます。逆に置換ダイアログでオプションを変更すると、引数の既定値がその値になります。引数を省略すると前回の値が引き継がれるため、すべての引数を指定するのをオススメします。

1

セルを置換する

Range の引数にセル名を指定すると、そのセルの範囲を置換できます。

セルの範囲「A1」~「A2」の B_ に置換するには Range("A1:A2").Replace("B", "_") を入力します。

Call Range("A1:A2").Replace("B", "_") ' B を _ に置換する
Call Range("A1:A2").Replace("B", "_", xlPart, xlByRows, False, False, False, False, False) ' B を _ に置換する

変更前

2

変更後

3

すべてのセルを置換

シート全体のセルを置換するには Cells.Replace を入力します。

Call Cells.Replace("B", "_") ' すべてのセルを置換する

ワイルドカード

引数「What」には、次のワイルドカードを使用できます。

パターン 説明 使用例 一致例
*任意の長さの文字"あ*"あ, あい, あいう
?任意の 1 文字"あ?"あい, あか, あき
~ワイルドカードの文字 * ? を検索する"あ~?~*"あ?*
Call Range("A1").Replace("A*", "_") ' A から始まるセルを _ に置換する
Call Range("A1").Replace("*", "_")  ' 空のセル以外を _ に置換する
Call Range("A1").Replace("", "_")   ' 空のセルを _ に置換する

数式を置換

何もしなくても数式を対象にしているため Range.Formula の値を置換しています。そのため数式を入力していないときはセルの値を置換します。

SUM 関数」を「AVERAGE 関数」に置換するには次のようにします。

Call Range("A1").Replace("SUM(", "AVERAGE(")
' =SUM(…) が =AVERAGE(…) のようになる
Range.Formula の値を取得するには「セルの関数や数式を取得する」をご覧ください。

書式を検索条件にする

引数「SearchFormat」に True を指定すると、Application.FindFormat プロパティと同じ書式のセルのみ置換対象にできます。

セルの範囲「A1」~「A2」の中で文字色が赤のセルを置換するには次のようにします。

' 文字色が赤を検索条件にする
Application.FindFormat.Clear                       ' 前回の書式をクリア
Application.FindFormat.Font.Color = RGB(255, 0, 0) ' 文字色

Call Range("A1:A2").Replace("B", "_", SearchFormat:=True) ' B を _ に置換する

変更前

4

変更後

5
書式については「セルの書式設定を取得または設定する」をご覧ください。

書式を置換する

引数「ReplaceFormat」に True を指定すると、Application.ReplaceFormat プロパティに設定されている書式に置換します。

文字色を赤に置換するには次のようにします。

' 文字色が赤を検索条件にする
Application.ReplaceFormat.Clear                       ' 前回の書式をクリア
Application.ReplaceFormat.Font.Color = RGB(255, 0, 0) ' 文字色

Call Range("A1").Replace("*", "", ReplaceFormat:=True) ' 書式を置換する

変更前

6

変更後

7

置換せれる書式は Application.ReplaceFormat に設定した書式のみです。文字色のみ設定したときは、文字色だけが変更され、それ以外のフォントサイズなどはそのままです。

書式を置換する対象となるセルは、引数「What」の検索文字が存在するセルが対象になります。そのためワイルドカードの * を使用して、すべてのセルが置換されるようにしています。ただし空のセルの書式は変更されません。

Call Range("A1").Replace("*", "", ReplaceFormat:=True) ' 空のセル以外の書式を置換する
Call Range("A1").Replace("", "", ReplaceFormat:=True)  ' 空のセルの書式を置換する

引数「Replacement」に空文字 "" を指定にして、引数「ReplaceFormat」に True を指定すると、セルの値が置換されません。書式だけを置換します。

別シートのセルを置換する

シートを指定するには Worksheets の引数にシート名またはインデックスを指定します。

Sheet1 を指定するには Worksheets("Sheet1") または Worksheets(1) を入力します。

sheet1
' Sheet1 のセル「A1」を置換
Call Worksheets("Sheet1").Range("A1").Replace("B", "_")
Call Worksheets(1).Range("A1").Replace("B", "_")

' Sheet2 のセル「A1」を置換
Call Worksheets("Sheet2").Range("A1").Replace("B", "_")
Call Worksheets(2).Range("A1").Replace("B", "_")

Sheet1 に Range や Cells を入力すると、自身のシートのセルになります。自身を表す Me. が省略されています。

' どちらも同じコード
Range("A1").Value = "Sheet1"
Me.Range("A1").Value = "Sheet1"

標準モジュールに Range や Cells を入力すると、開いているシートのセルになります。アクティブシートを表す ActiveSheet. が省略されています。

' どちらも同じコード
Range("A1").Value = "アクティブ"
ActiveSheet.Range("A1").Value = "アクティブ"