Range(左上セル, 右下セル)の使い方

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

セルを指定するRangeには、2つの書き方があります。ひとつは、お馴染みの

Range("A1")

Range("A1:B3")

などです。そして、もうひとつが、動的なセル範囲を特定するときに役立つ

Range(左上セル, 右下セル)

という書き方です。これ、実務ではよく使います。てゆーか、これを使えないと実務ではかなり困ったことになります。

まずは、基本的な使い方から解説しましょう。

上図のように選択するには、たとえば次のようにします。

Sub Sample1()
    Range("A4:C4").Select
End Sub

これを、Range(左上セル, 右下セル)で表すと、次のようになります。

Sub Sample2()
    Range(Range("A4"), Range("C4")).Select
End Sub

まぁ、実際にはこんな書き方をしませんが、要するにこういうことです。Range()の中に、またRangeとかCellsなどが入れ子になると。そういう使い方です。もちろん、Selectを別のメソッドやプロパティに変えれば、1行のセル範囲をまとめて操作できます。

Sub Sample3()
    Range(Range("A4"), Range("C4")).Copy Range("E2")
End Sub


Sub Sample4()
    Range(Range("A4"), Range("C4")).Font.ColorIndex = 3
End Sub

では、これを使って、セルC8にSUM関数を代入してみましょう。こういうの、実務ではよくありますよね。ああ、もちろんC列のデータは、何行目まで入力されているか分からないという前提です。それが実務です。

先日も、セミナーを受講した方から、こうした質問を受けたのですが、こんなときは「データの数が固定だったら」というところから考えます。もし合計するセル範囲がC2:C7と固定されていたら

Sub Sample5()
    Range("C8") = "=SUM(C2:C7)"
End Sub

だけで済みます。こうして、単純なケースから発想をスタートさせるのが、マクロを作るときのポイントです。さて、ではまず、SUM関数を代入するセル(ここではセルC8)が分からなかったらどうしましょうか。SUM関数を代入するのは、C列に入力されている最終セルの、さらに1つ下のセルです。C列の最終セルは、Endモードで一発ですね。

Range("C2").End(xlDown) の 1つ下のセル

ここでは"あえて"、Endモードの基点をセルC2にしました。「1つ下のセル」は、Offsetを使えばいいです。

Range("C2").End(xlDown).Offset(1, 0) = "=SUM(C2:C7)"

代入する "=SUM(C2:C7)" を、次のように分解して考えてみます。

"C2:C7" のところは、計算する範囲のアドレスです。もし、このセル範囲が固定されていたなら、ここは Range("C2:C7") のことです。欲しいのは、そのセル範囲のアドレスですよね。

セルのアドレスを調べるには、Addressプロパティを使います。

しかし、実務ではデータの数が決まっていませんから、Range("C2:C7") と決め打ちすることができません。さあ、ここで登場するのが、Range(左上セル, 右下セル) です。今回は長方形ではなく、1列の選択ですから、Range(上セル, 下セル)と考えましょう。

Range("C2:C7") ということは、

上セル → Range("C2")

下セル → Range("C7")

ですよね。そのまま組み込むと

Range(Range("C2"), Range("C7"))

となります。

実際には、C列のデータ数が分かりませんので、Range("C7")を決め打ちできません。でもこのセルって、Range("C2")からEndモードで下に向かってジャンプして、どこだか分からないけど行き着くであろうセル、のことでしょ。

上セル → Range("C2")

下セル → Range("C7") → Range("C2").End(xlDown)

したがって、こうなります。

Range(Range("C2"), Range("C2").End(xlDown))

ここまでをまとめると、SUM関数を代入するマクロは、次のようになります。

Sub Sample6()
    Range("C2").End(xlDown).Offset(1, 0) = _
        "=SUM(" & Range(Range("C2"), Range("C2").End(xlDown)).Address & ")"
End Sub

長いので折り返しました。でも、よく見るとこれ、Range("C2")が何度も出てきませんか？てゆーか、わざとそういう風にやったんですけど(笑)。このように、Endモードの基点や、SUM関数で計算する上セルなどを統一しておくと、このRange("C2")を何とかしてやる"工夫の余地"が生まれます。オブジェクト型変数を使いましょう。

Sub Sample7()
    Dim A As Range
    Set A = Range("C2")
    A.End(xlDown).Offset(1, 0) = "=SUM(" & Range(A, A.End(xlDown)).Address & ")"
End Sub

このように、基点セルをオブジェクト型変数に入れてやれば、コードが短くなるだけでなく、SUM関数を代入する列が変わっても、Set A = Range("C2") だけを調整してやればいいですね。いずれにしても、ここでのポイントは、Range(左上セル, 右下セル) です。

ちなみに、上記の解説では、SUM関数に絶対参照のアドレスが指定されます。もちろん相対参照で指定することも可能です。その方法は、ヘルプで調べてください。

別シートを扱うとき

実務で大活躍する Range(左上セル, 右下セル) ですが、ひとつ大きな落とし穴があります。ほとんど人が、この落とし穴にはまるといっても過言ではありません。それは、アクティブシートではない、別のシートを扱う場合です。

まずは復習です。セルを表す Range や Cells などは「ブック → シート → セル」の階層構造を省略すると、アクティブシートのセルという意味になります。

もし、アクティブシートではないセルを操作するのなら、どのシートの～という階層構造を指定します。

では、Range(左上セル, 右下セル) で考えてみましょう。

ではこれを、アクティブシートがSheet2だったとき、アクティブシートではないSheet1のセル範囲を指定するとしましょう。そのとき、次のように書いてエラーになることが多いです。

Sub Sample8()
    Sheets("Sheet1").Range(Range("A3"), Range("C3")).Copy
End Sub

なぜエラーになるか分かりますか？これは、次のように指定しているからです。

Sheet1のセル範囲で、その左上が別のシートのセルA3、右下も別のシートのC3…なんてことは、あり得ません。シートが違うんですから。つまり、

ということです。なので、先のエラーになったコードは

Sub Sample9()
    Sheets("Sheet1").Range(Sheets("Sheet1").Range("A3"), Sheets("Sheet1").Range("C3")).Copy
End Sub

と書かなければなりません。もちろん、これでは Sheets("Sheet1") を連呼していて可読性もメンテナンス性も悪いですから、次のように工夫したいところです。

Sub Sample9()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    ws.Range(ws.Range("A3"), ws.Range("C3")).Copy
End Sub

あるいは

Sub Sample9()
    With Sheets("Sheet1")
        .Range(.Range("A3"), .Range("C3")).Copy
    End With
End Sub

みたいに。