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

はじめに

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

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

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

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

Replace メソッド

Replace(What, Replacement)

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

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

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

セルを置換する

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 を _ に置換する

変更前

変更後

すべてのセルを置換

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

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

ワイルドカード

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

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(…) のようになる

書式を検索条件にする

引数「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 を _ に置換する

変更前

変更後

書式を置換する

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

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

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

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

変更前

変更後

置換せれる書式は 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 のセル「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 = "アクティブ"