Excelマクロ・VBA 絶対参照を一括で設定・解除する方法

絶対参照の設定解除は通常、

セル入力中でしか行えず

一括での設定・解除は不可能です。

しかしマクロ・VBAを使用すれば

まとめて設定・解除が可能になります。

この記事ではその方法を紹介します。

サンプルファイル

使用方法

※セキュリティの警告が出る場合は有効化をお願いします。(解除方法↓)

 また「開発」メニューがない場合は追加をお願いします。

サンプルファイルに登録されている

マクロを利用します。

参照の形式を変えたい

セル範囲を選択し

「開発」メニューより

「マクロ」を選択します。

「開発」メニューより「マクロ」を選択します。

変換方法が4つ提示されるため

適切なものを選び「実行」を押下します。

すると選択した通りの変換が実行されます。

R1C1形式でも問題なく使用可能です。

マクロウインドウから変換方法を選択

コードサンプル

コードは下記のとおりです。

標準モジュールを追加してペーストすれば

任意のブックで使用可能です。

Option Explicit
Sub 選択範囲を行列とも絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlAbsolute)
End Sub
Sub 選択範囲を行のみ絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlAbsRowRelColumn)
End Sub
Sub 選択範囲を列のみ絶対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlRelRowAbsColumn)
End Sub
Sub 選択範囲を行列とも相対参照に変換()
    Call 指定範囲を指定参照形式に変換(Selection, XlReferenceType.xlRelative)
End Sub

Private Sub 指定範囲を指定参照形式に変換(ByVal 指定範囲 As Range, ByVal 参照形式 As XlReferenceType)
    
    Dim 変換対象セル As Range
    ' 選択範囲を全セルを操作
    For Each 変換対象セル In 指定範囲
        
        'セルが数式の場合のみ変換を行う
        If 変換対象セル.HasFormula Then
            変換対象セル.Formula = _
                Application.ConvertFormula( _
                    Formula:=変換対象セル.Formula, _
                    FromReferenceStyle:=xlA1, _
                    ToReferenceStyle:=xlA1, _
                    ToAbsolute:=参照形式)
        End If
        
    Next 変換対象セル
End Sub

「指定範囲を指定参照形式に変換」

という共通関数を用意し

それぞれのマクロから選択範囲と

参照形式を引数として与えています。

そして全てのセルに対して

数式セルであれば形式変換をかけています。

(数式セルでないと不正な動作をするため)

関連記事

マクロ・VBA 最初のプログラム

数式がずれないようにコピー(オートフィル)する方法

連続データを効率的にセル入力する方法(オートフィルとフラッシュフィル)

フォローする