セルの範囲を指定するVBA - Officeの魔法使い

VBAでセルの範囲を指定する方法がたくさんあり、かつ、利用するシチュエーションが多いので、その都度どんな指定の方法がいいのか悩む時もあります。
ここのところVBAでセル操作をしまくって分かったそれぞれの特徴を書きます。

Range
Cells
CurrentRegion
Offset
Resize
添え字
Rows
Columns
Areas
Item
EntireRow
EntireColumn
SpecialCells
Intersect
Union
ActiveCell
Selection
Address
Count
Row
Column
組み合わせる

Range

Rangeはセル選択の基本です。
Range(“A1”)のように、セル参照を文字列で指定することでそのセルを指定することができます。
1つのセルだけではなく、Range(“A1:B10”)のように、セル範囲を指定することもできます。
また、Range(“A1:B” & 行数)のように、変数でセルやセル範囲を指定することもできます。
Rangeでしかできないとこととしては、Range(Range(“A5”),Range(B10))のように二つのセルの指定を組み合わせてその範囲を表すことができるという特徴があります。後述するCellsなどと組み合わせて可変するセル範囲を指定するのに便利です。

Cells

Cellsは1つのセルを指定するもので、行番号、列番号で指定できるので、ループ処理のループ回数によってセルを指定するのに便利です。
Cells(1,1)はセルA1です。
Cells(5,2)は5行目2列目なのでセルB5です。
行番号は数値のみでしか指定できませんが、列番号は列を表すアルファベット文字でも指定できます。
Cells(3,”C”)はセルC3を表します。
次のVBAではD5からD10の値を次々とメッセージボックスで表示します。

For 行番号=5 To 10
  Msgbox Cells(行番号,"D").Value
Next

Rangeと組み合わせて、次のVBAではA2からA10までのセル参照を連続してメッセージボックスで表示します。

For 行番号=2 To 10
  Msgbox Range(Range("A2"),Cells(行番号,"A")).Address
Next

CurrentRegion

ExcelでCtrl+Aのショートカットキーを押すと、全体が選択されるのではなくアクティブセルに対して連続したセル範囲を選択します。
C5からE10までのセルに入力されている状態でセルD8をクリックして、Ctrl+AをクリックするとC5からE10までの範囲が選択されます。多少セルに入力されていなくても連続した範囲と認識して、四角の範囲を選択します。
CurrentRegionは、はじめに指定したセルに対して連続した範囲を選択します。
上記の範囲であれば、Range(“D8”).CurrentRegionで表すことができ、Range(“C5:E10”)と同じことができます。
指定されたセルに対して可変する大きさの範囲を指定するのに便利です。

Offset

Offsetは、指定したセルや範囲に対して、下にいくつ、右にいくつ移動した範囲を指定します。
Range(“A1”).Offset(1,2)はセルA1に対して、下に一つ、右に一つ移動したセルなのでセルB3 を指定したことになります。
移動する行数と列数を指定しますが、あくまで移動する量を指定するので、Offset(1,1)ではその元のセルを指定するのではなく、下1個、右1個ずれたセルを指定するので注意が必要です。ずれないときはOffset(0,0)と指定します。
範囲に対しても有効なのでRange(“A1:C3”).Offset(2,0)で、A3からC5の範囲を選択します。

Resize

Resizeは、指定したセルに対して、大きさを増やしたり減らしたりします。
Range(“A1”).Resize(2,3)はA1からC2のセル範囲を示します。
Range(“A1:C5”).Resize(4,2)はA1からB4のセル範囲を示します。
元のセルの左上を基準に増減させます。

添え字

セルには1番目から番号が振られています。何番目のセルかは添え字で指定できます。
Range(“A1:C10”)(1)で1番目を表す添え字で表すのは1つ目のセルA1です。
Range(“A1:C10”)(30)は全部で30セルあるうちの最後のセルなのでセルC10を表しています。
またRange(“A1:C10”)(1,2)のように縦と横の2つの数値で表すこともできます。これは下に1個目、右に2個目なのでセルB1を表します。

Rows

行を表すのがRowsです。
Range(“A1:C10”).Rowsで全ての行という意味になりFor Each文などで活用できます。
Range(“A1:C10”).Rows(5)でA1からC10のうちの5行目という意味になるので、A5からC5の範囲を表します。
実は、Rowsははじめにセル範囲がなくても指定できます。
Range(“A50:B100”).Rows(1)はA50からB100の1行目である50行目を指定しますが、単独でRows(1)と書くとシート全体の1行目を指定できます。
複数行も指定できますが、複数行を指定する場合はRows(“1:5”)のように文字列として指定します。単独でRows(“1:5”)とも書けますし、Range(“A10:Z100”).Rows(“1:5”)という書き方もできます。1行目と5行目だけを指定できないかとRows(“1,5”)とも書いてみましたが、どこを指定するのかよくわかりませんでした。

Columns

列を表すのがColumnsで、Rowsと同じようにFor Each文に使えます。
Range(“A1:C10”).Columns(2)でA1からC10のうちの2列目という意味になるので、B1からB10の範囲を表します。
Rowsと同じようにColumnsも単独で指定でき、シート全体の何列目を指定できます。Columns(5)はE列を表します。複数列はColumns(“A:B”)のようにアルファベットの列番号を指定します。Range(“C1:Z100”).Columns(“A:B”)という指定もできて、Aはあくまで1列目を表すようなので、C1からD100を選択します。この指定は混乱の元ではあります。

Areas

AreasはRange(“A1:B2,E1:F2”)のように離れた範囲の指定に対して、どちらの範囲を指定するかを指定します。
Range(“A1:B2,E1:F2”).Areas(1)は2つに離れている範囲の1つ目であるRange(“A1:B2”)になります。

Item

添え字によって、セル範囲の何番目、何行目、何列目、何エリア目を表すことができますが、正式には、添え字の( )の前にItemが必要なのです。
ItemはItem(1)と1つだけ指定する場合とItem(2,3)のように2つ指定する場合があります。1つだけの指定であれば単純に何番目です。2つ指定の場合は縦、横の順番で指定します。
Range(“A1:C10”).Item(1)はセルA1です。Range(“A1”).CurrentRegion.Item(3,5)は連続した範囲なのであればセルE3です。Areasはあくまで順番指定で縦横の概念がありませんので、Range(“A1:C10,A100:C200,E1:F10,E100:F200”).Areas.Item(2,1)のように2つ指定することはできません。

EntireRow

EntireRowはその前のRangeの行全体を指定します。Range(“H5”).EntireRowは5行目を選択します。
セルB5、C8、E10を選択したうえで、Selection.EntireRow.Deleteで5行目、8行目、10行目を一気に削除できます。

EntireColumn

EntireColumnはその前のRangeの列全体を指定します。Range(“H5”).EntireColumnはH列全体を選択します。
セルB5、C8、E10を選択したうえで、Selection.EntireColumn.DeleteでB列、C列、E列を一気に削除できます。

SpecialCells

SpecialCellsはExcelのCtrl+Gで表示されるジャンプダイアログボックスのセル選択ボタンをしたときに指定できるセルを表します。

SpecialCellsは、単にSpecialCellsだけでは指定できず、前にRangeやCellsのようなセル指定が必要です。後ろには( )でどの種類を調べるかの文字を指定します。
全セルの中で数式が入っているセルを指定する場合は、Cells.SpecialCells(xlCellTypeFormulas)、セルA10からE100の中にある数式が入っているセルを指定する場合はRange(“A10:E100”).SpecialCells(xlCellTypeFormulas)となります。数式が入っているセルのみを飛び飛びで選択してくれます。
中でもよく使うのは最終セルです。一度セルに書き込んだ後にDeleteで消してもそのセルは使っているということになっており、CurrentRegionでは連続した範囲ですが、過去にさかのぼり使っている右下のセルを最終セルとしています。Cells.SpecialCells(xlCellTypeLastCell)で指定することができます。Rangeと組み合わせてRange(Range(“A1”),Cells.SpecialCells(xlCellTypeLastCell))で使用しているセル範囲を指定できます。

Intersect

Intersectは2つのセル範囲において、重複したセルを指定するのに使います。Intersect(Range(“A1:C3”), Range(“B2:D4”))は、両方のセル範囲に入っているB2からC3のセル範囲を表します。
Intersectは、指定したセルが対象範囲内か調べるのに使うことも多いです。
アクティブセルがテーブル「売上テーブル」の範囲内かどうか判断して動作を変える時は次のようにします。

If Intersect(Range("売上テーブル"),ActiveCell) Is Nothing Then
  <アクティブセルが売り上げテーブル範囲内にはなかった時の処理>
Else
  <アクティブセルが売り上げテーブル範囲内の時の処理>
End If

Union

Unionは複数の範囲を結合します。
Union(Range(“A1:E5”),Range(“D7:E8”))だと、A1からE5とD7からE8のセル範囲を指定します。
本来は一つの一覧表の下に同じ項目数の別の一覧表を付け足すというのが目的です。
次のVBAでは1行目から10行目にあわせて、それ以下の行でA列がxかどうか判断しxならば対象行に加えていって、最後に対象行黒で塗りつぶすという動作をします。

Sub a()
  Dim rng As Range
  Dim i As Long
  Set rng = Range("A1:E10")
  For i = 11 To 20
    If Cells(i, "A").Value <> "x" Then
      Set rng = Union(rng, Cells(i, 1).Resize(1, 5))
    End If
  Next
  rng.Interior.Color = vbBlack
End Sub

ActiveCell

ActiveCellは今、選択している1個のセルを表します。入力した時に入力されるセルのことです。範囲選択していても、その中の白黒反転表示されているセルがありますが、このセルを指します。
ActiveCell.CurrentRegionでアクティブセルに対する連続範囲を選択できます。
ものすごく大きな範囲で、この表の先頭に戻りたいなというときは、ActiveCell.CurrentRegion(1).Selectで戻れます。

Selection

ActiveCellと同じく選択しているものを指定するのに結構使うのがSelectionです。こちらは範囲を指定できます。
Selectionは選んでいるものを指します。ということは、実はセル以外でもグラフや図形も選んでいたらSelectionになるので、セル範囲だと思ってSelectionを使うとエラーになる場合もあります。そのため、Selectionがセル範囲なのかどうかを判定してから使うことがお勧めです。
TypeNameでデータ型を調べることができるので、Selectionのデータ型がセルを表す「Range」ならセル範囲ということになります。

If TypeName(Selection)="Range" Then
  処理
End If

でも実際には一回一回やっているとプログラムがだらだらと長くなるので、私はグラフが混在しているとか必要な時にしかやってません。
Selection.Copy Range(“D1”)で選んだセルをどんどんD1以降に貼り付けることもできます。

Address

Addressはその範囲を文字列で表現してくれます。
Cells(2,3).Addressは「$C$2」になります。
Range(Range(“A1”),Cells(2,3)).Addressは「$A$1:$C$2」になります。
絶対参照の$が初期設定ではついてしまうのですが、Addressの引数を設定すれば$なしでも出力できます。Cells(2,3).Address(false,false)で行列とも相対参照でC3、Cells(2,3).Address(false,true)で行のみ相対参照でC$3、Cells(2,3).Address(false,true)で列のみ相対参照で$C3を出力します。
Addressはセル参照文字列を答えとして出すので、それ以上「.」を使って何かを調べることはありません。

Count

個数を数えます。セル範囲であればセル個数を、行範囲であれば行数を列範囲であれば列数を求めます。セル以外でもシート数を数える時にも使います。
Range(“A1”).Countはセル個数が1個だから1、Range(“A1:C10”).Countはセル個数が30個だから30です。
Range(“A1:V100”).Rows.CountはセルA1からV100までの行の個数なので100、Range(“A1:E20”).Columns.CountはセルA1からE20までの列の個数なので5です。
前のCurrentRegionと添え字を使って最終セルを求めることができます。Range(“A1”).CurrentRegion(Range(“A1”).CurrentRegion.Count)はセルAから始まる連続範囲の最終セルです。
Range(“A1:B2,E1:F2”).Areas.Countだと離れた範囲が2つあるので2が求まります。
Countも個数という答えを出すのでその後に「.」を使って何かを調べることはありません。

Row

そのセルの行番号を表します。セル範囲を指定していれば先頭セルの行番号を表します。
Range(“S1”).Rowは1です。Range(“S1:Z10000”).Rowも1です。
Rowsは行のセル範囲そのもの、Rowは行番号という違いがあります。
これも行番号という答えを出すのでその後に「.」を使って何かを調べることはありません。
Range(“A1:E3,Z10:AA11”).Columns.Countは1つ目のエリアの1行目から3行目の3となります。

Column

そのセルの列番号を表します。セル範囲を指定していれば先頭セルの列番号を表します。
Range(“C200”).Columnは3です。Range(“C5:X100”).Columnも3です。
これも列番号という答えを出すのでその後に「.」を使って何かを調べることはありません。
Range(“A1:E3,Z10:AA11”).Columns.Countは1つ目のエリアのA列からE列の5となります。

組み合わせる

以上を組み合わせていくと、可変されている範囲を指定したコピーなどが簡単に行えます。
シート1に次のような入力画面があります。

シート2に次のような入力内容を蓄積するシートがあります。

シート1に入力されている内容をシート2に転記するには、次のようなプロセスで行います。

シート1のB7からの連続範囲の行数を調べ、そこから6行目のタイトル行の1行を引いて入力されているデータ数とする。
シート2のA1からの連続範囲の行数を調べ、それに1を足し、今から蓄積するデータを転記する行とする。
シート1のセルC2のデータを転記する行のA列から入力されているデータ数の分の範囲にコピーする。
シート1のセルC4のデータを転記する行のB列から入力されているデータ数の分の範囲にコピーする。
シート1のB7からB7からの連続範囲の最終セルまでの範囲を、データを転記する行のC列からの3列の入力されているデータ数の分の範囲にコピーする。
次の入力のためにシート1のC2、C4、B7からD13までの内容をクリアする。

それを VBA にしたものが次の通りです。

Sub 転記()
  Dim sh1 As Worksheet
  Dim sh2 As Worksheet
  Dim 入力されているデータ数 As Long
  Dim 転記する行 As Long
  Set sh1 = ThisWorkbook.Sheets("シート1")
  Set sh2 = ThisWorkbook.Sheets("シート2")
  '1
  入力されているデータ数 = _
        sh1.Range("B7").CurrentRegion.Rows.Count - 1
  '2
  転記する行 = _
        sh2.Range("A1").CurrentRegion.Rows.Count + 1
  '3
  sh1.Range("C2").Copy _
       sh2.Cells(転記する行, "A").Resize(入力されているデータ数, 1)
  '4
  sh1.Range("C4").Copy _
       sh2.Cells(転記する行, "B").Resize(入力されているデータ数, 1)
  '5
  sh1.Range( _
       sh1.Range("B7"), _
       sh1.Range("B7").CurrentRegion( _
         sh1.Range("B7").CurrentRegion.Count _
       ) _
     ).Copy _
       sh2.Cells(転記する行, "C").Resize(入力されているデータ数, 3)
  '6
  sh1.Range("C2,C4,B7:D13").ClearContents
  sh1.Range("C2").Select
End Sub

ステップ5のコピー元範囲を求めるのに、セルB7からの連続範囲の個数を求め、セルB7からの連続範囲の最終セルを求め、セルB7からそこまでと求めるのまでの流れが難しいかもしれません。