最終セルの下にSUM関数を自動挿入する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

たとえばA列に、下図のようなデータが入力されていたとします。

A列には全部で6個の数値が入力されていて、最終セルの「52」はセルA7です。このように数値が入力されている表の最終行にSUM関数を挿入してみましょう。つまり、次のような感じです。

セルに数式を挿入するだけなら簡単ですね。今回のケースなら

Sub Sample1()
    Range("A8") = "=SUM(A2:A7)"
End Sub

でOKです。しかし、データが入力されている最終セル(ここではセルA7)がマクロを実行するたびに異なる場合はどうでしょう。

まず、SUM関数を挿入するセルを特定します。SUM関数を挿入するのは「最終セルの1つ下」のセルですね。データが入力されている最終セルは、Endモードで取得できます。

  • 最終セル → Range("A2").End(xlDown)

ここは、後の処理を楽にするため、あえてRange("A2")を基点にしました。SUM関数を入力するのは、この最終セルの「1つ下」のセルですから

  • SUM関数を挿入するセル → Range("A2").End(xlDown).Offset(1, 0)

となります。さて、この「SUM関数を挿入するセル」に

=SUM(A2:A7)

という数式を挿入したいのですが、この「A7」は上で調べた「最終セル」ですね。つまり「A2:A7」というのは

Range("A2")からRange("A2").End(xlDown)までのアドレス

ということになります。こんなとき、それぞれのアドレスを文字列で結合するのは美しくありません。ここでは、Rangeの

Range(始点セル, 終点セル)

というを使い方を活用します。つまり

Range(Range("A2"), Range("A2").End(xlDown)).Address

ということです。ただし、このままでは「$A$2:$A$7」が取得されてしまいますので、Addressプロパティが相対参照形式のアドレスを返すようにします。

Range(Range("A2"), Range("A2").End(xlDown)).Address(False, False)

以上をまとめると、最終セルの下にSUM関数を挿入するマクロは次のようになります。

Sub Sample2()
    Range("A2").End(xlDown).Offset(1, 0) = _
        "=SUM(" & Range(Range("A2"), Range("A2").End(xlDown)).Address(False, False) & ")"
End Sub

もちろん、これでも目的は達成できますが、Range("A2")を連呼していて、何だか見た目が悪いです。また、今回の基点セル(セルA2)が変更されたときのメンテナンス性も悪いですね。そこで、Range("A2")をWithステートメントでくくってやります。

Sub Sample3()
    With Range("A2")
        .End(xlDown).Offset(1, 0) = _
            "=SUM(" & Range(■, .End(xlDown)).Address(False, False) & ")"
    End With
End Sub

すいぶんスッキリしましたが、問題は■のところです。

Range(始点セル, 終点セル)

というRangeの使い方では、始点セルと終点セルに「Rangeオブジェクト」を指定するとヘルプに書かれています。今回■にはRange("A2")を指定したいのですが、Withでくくっていますので、■にRange("A2")をオブジェクトとして指定するのは、簡単ではありません。

実は、

Range(始点セル, 終点セル)

の始点セルと終点セルは、Rangeオブジェクトだけでなく、Rangeオブジェクトを表すセルのアドレスを文字列で指定することもできます。つまり、

Range("A2", "A7")

みたいな指定も可能なのです。であれば、■のところにRange("A2")のアドレスを指定すればいいのですから

Sub Sample3()
    With Range("A2")
        .End(xlDown).Offset(1, 0) = _
            "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"
    End With
End Sub

となります。

これなら、基点のセルA2を変更すれば、挿入されるSUM関数の位置も、SUM関数で指定する引数のアドレスも、すべて自動的に変化します。