【VBA】Excelで数式を削除して値と書式をそのまま残す方法

この記事では、VBAを使ってExcelのワークシートから数式を削除し、値と書式をそのまま保持する方法をご紹介します。まず選択したセル範囲から数式を削除する方法を学び、次にワークシート全体から数式を削除する方法を解説します。

VBAで数式を削除するコード(クイック概要)

Sub Remove_Formulas_from_Selected_Range()

Dim Rng As Range

Set Rng = Selection

Rng.Copy

Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False

End Sub

コードの解説

• このコードは、Remove_Formulas_from_Selected_Rangeという名前のマクロを作成します。
• まず、ユーザーが選択した範囲内のすべてのセルの値をコピーします。
• 次に、書式を維持したまま各セルに値を貼り付けます。
• これにより、選択したセル範囲からすべての数式が削除され、値と書式だけが残ります。

VBAで数式を削除して値と書式を保持する2つの方法

ここでは、「Jupyter Group」という会社の従業員の氏名、初任給、現在の給与を含むデータセットを例に説明します。

また、ワークシートの別々のセルには、平均給与、最高給与者の氏名、最低給与者の氏名も表示されています。

この例では、各従業員の現在の給与は初任給の20%増しとして計算されています。

つまり、セルD4には次の数式が入力されています。

=C4+(C4*20)/100

同様に、セルD5には以下の数式が入っています。

=C5+(C5*20)/100

以下同様です。

セルG7には平均給与が、次の数式で計算されています。

=AVERAGE(D4:D13)

セルH7には最高給与の従業員名が、次の数式で表示されています。

=INDEX(B4:D13,MATCH(MAX(D4:D13),D4:D13,0),1)

そして、セルI7には最低給与の従業員名が、次の数式で表示されています。

=INDEX(B4:D13,MATCH(MIN(D4:D13),D4:D13,0),1)

今回は、Visual Basic Application(VBA)を使用してマクロを作成し、このワークシートから数式を削除しながら、値と書式はそのまま維持できるようにします。

方法1: 選択したセル範囲から数式を削除するVBA

まず、ワークシート全体ではなく、特定のセル範囲から数式を削除するマクロを作成しましょう。

例として、従業員記録の部分(B4:D13)のみから数式を削除してみます。

VBAコード:

Sub Remove_Formulas_from_Selected_Range()

Dim Rng As Range

Set Rng = Selection

Rng.Copy

Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False

End Sub

※ポイント: このコードは、Remove_Formulas_from_Selected_Rangeという名前のマクロを作成します。

実行手順と結果

まず、ファイルをマクロ有効ブック(.xlsm)形式で保存してください。その後、数式を削除したいセル範囲を選択します。

ここでは従業員記録(B4:D13)のみから数式を削除したいので、範囲B4:D13を選択します。

次に、Remove_Formulas_from_Selected_Rangeというマクロを実行します。

(マクロの実行方法の詳細については、関連記事をご参照ください。)

これで、選択したセル範囲からすべての数式が削除され、値と書式だけが残ります。

あわせて読みたい: Excelで形式を選択して貼り付けなしで数式を値に変換する5つの簡単な方法

関連記事

• Excelでフィルター時に数式を削除する3つの方法
• Excelで自動入力される数式を削除する5つの方法
• Excelで数式が自動的に値に変換されるのを防ぐ方法
• Excelで数式を値に変換する8つのクイックな方法

方法2: ワークシート全体から数式を削除するVBA

前の方法では、選択したセル範囲から数式を削除するマクロを作成しました。

今度は、ワークシート全体から数式を削除してみましょう。

その場合は、以下のVBAコードを使用します。

VBAコード:

Sub Remove_Formulas_from_the_Whole_Worksheet()

Sheet_Name = InputBox("Enter the Name of the Worksheet to Remove Formulas: ")

Dim Rng As Range

Set Rng = Sheets(Sheet_Name).Cells

Rng.Copy

Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False

End Sub

※ポイント: このコードは、Remove_Formulas_from_the_Whole_Worksheetという名前のマクロを作成します。

実行手順と結果

ワークシートに戻り、Remove_Formulas_from_the_Whole_Worksheetというマクロを実行します。

すると、数式を削除したいワークシートの名前を入力するよう求めるインプットボックスが表示されます。

ここではSheet2から数式を削除したいので、「Sheet2」と入力します。

OKをクリックします。

これで、ワークシート全体から数式が削除され、値と書式だけが残ります。

従業員の現在の給与からも数式が削除されました。

また、平均給与、最高給与者、最低給与者のセルからも数式が削除されています。

あわせて読みたい: Excelで数式の代わりに値を表示する7つの方法

覚えておきたいポイント

今回のコードでは、VBAでの貼り付けにxlPasteValuesAndNumberFormatsオプションを使用しています。このオプションを使うと、Excelで値を貼り付ける際に、値と書式をそのまま維持できます。

これ以外にも、VBAには値を貼り付けるためのオプションが11種類あり、それぞれ異なる種類の貼り付けを行います。

詳細については、Microsoftの公式ドキュメントをご確認ください。

まとめ

これらの方法を使えば、VBAでExcelのワークシートから数式を削除しながら、値と書式を変更せずにそのまま残すことができます。ご不明な点があれば、お気軽にお問い合わせください。

関連記事

• Excelで数式の結果を文字列に変換する7つの簡単な方法
• Excelで数式の結果を別のセルに入れる4つの一般的なケース
• Excelで数式ではなくセルの値を返す3つの簡単な方法
• Excelで数式を自動的に値に変換する6つの効果的な方法
• Excelで非表示の数式を削除する5つのクイックな方法