Excel VBA 範囲とセル

VBAの範囲とセル

Excelの表計算ソフトは、データをセルに格納します。セルは、行と列に並べられています。各セルは、その行と列の交点で識別できます。（例：B3またはR3C2）Excelの範囲とは、1つ以上のセルのことを指します。（例：A3:B4）

セルのアドレス

A1表記

A1表記では、セルは列の文字（AからXFDまで）の後に行番号（1から1,048,576まで）で参照されます。これをセルのアドレスと呼びます。 VBA では、Range オブジェクトを使用して任意のセルを参照することができます。

'現在アクティブなシートのセル B4 を参照する
MsgBox Range("B4")

'「Data」というシートのセル B4 を参照する
MsgBox Worksheets("Data").Range("B4")

'「My Data」という名前の別のワークブックの「Data」というシートのセル B4 を参照する
MsgBox Workbooks("My Data").Worksheets("Data").Range("B4")

R1C1表記

R1C1表記では、セルはRの後に行番号、そして文字’C’の後に列番号を付けて参照されます。VBAでは、R1C1記法を使用するために、Cellsオブジェクトを使用します。

'R[6]C[4]、つまりD6を参照する
Cells(6, 4) = "D6"

セルの範囲

A1表記

複数のセルを参照する場合は、開始セルと最終セルの間に「:」を使用します。以下は、A1 から D10 までのすべてのセルを参照する場合です。

Range("A1:D10")

R1C1表記

複数のセルを参照する場合は、開始セルアドレスと最終セルアドレスの間に「,」を使用します。次のコードは、A1 から D10 までのすべてのセルを参照します。

Range(Cells(1, 1), Cells(10, 4))

セルへの書き込み

セルまたは連続したセル群に値を書き込むには、単純に範囲を参照し、=記号を付けてから、格納する値を書き込みます。

'F6のセルに"F6"を格納する
Range("F6") = "F6"

'R[6]C[5]すなわちE6のセルに"E6"を格納する
Cells(6, 5) = "E6"

'A1:D10の範囲に"A1:D10"を格納する
Range("A1:D10") = "A1:D10"
'または
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

セルからの読み込み

セルから値を読み出すには、値を格納する変数を参照し、=記号を付けて、読み出す範囲を参照するだけです。

Dim val1
Dim val2

' セルF6から読み込む
val1 = Range("F6")

' セルE6から読み込む
val2 = Cells(6, 5)

MsgBox val1
Msgbox val2

注：セルの範囲から値を格納するには、単純な変数ではなく、配列を使用する必要があります。

非連続セル

連続しないセルを参照するには、セルアドレスの間にカンマを使用します。

'セルA1、A3、およびA5に10を格納する
Range("A1,A3,A5") = 10


'セルA1:A3 および D1:D3 に 10 を格納する
Range("A1:A3, D1:D3") = 10

セルの交点

交差するセルを参照する場合は、それらのセルのアドレスの間にスペースを入れてください。

'A1:D10とD1:F10の間にある共通部分であるD1:D10に"Col D"を格納する
Range("A1:D10 D1:G10") = "Col D"

セルまたは範囲からのオフセット

Offset 関数を使用すると、指定した範囲（セルまたはセル群）から、指定した行数および列数だけ参照を移動させることができます。

Offsetの構文

Range.Offset(number_of_rows, number_of_columns)

セルからのオフセット

' セルA1からのOFFSET
' セルそのものを参照する
' 0行0列を移動
Range("A1").Offset(0, 0) = "A1"

' 1行0列を移動
Range("A1").Offset(1, 0) = "A2"

' 0行1列を移動
Range("A1").Offset(0, 1) = "B1"

' 1行1列を移動
Range("A1").Offset(1, 1) = "B2"

' 10行5列を移動
Range("A1").Offset(10, 5) = "F11"

範囲のオフセット

' セルの範囲A1:D4を4行4列分だけ移動させる
' 新しい参照先は E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

範囲への参照の設定

Range変数に範囲を割り当てるには、Range 型の変数を宣言し、Set コマンドで設定します。RANGE はオブジェクトなので、SET コマンドが必要であることに注意してください。

' Range変数を宣言する
Dim myRange as Range

' 変数に範囲 A1:D4 を設定する
Set myRange = Range("A1:D4")

' $A$1:$D$4を表示する
MsgBox myRange.Address

範囲のサイズ変更

RangeオブジェクトのResizeメソッドは、参照する範囲のサイズを変更します。

Dim myRange As Range

' 元の範囲
Set myRange = Range("A1:F4")

' $A$1:$E$10を表示する
Debug.Print myRange.Resize(10, 5).Address

リサイズ後の範囲の左上セルと元の範囲の左上セルが同じになります。

Resizeの構文

Range.Resize(number_of_rows, number_of_columns)

OFFSETとResize

オフセットは、範囲の寸法を変更せず、指定された行と列の数だけ移動させます。リサイズは、元の範囲の位置は変えず、寸法を指定された行数および列数に変更します。

シート内のすべてのセル

Cellsオブジェクトは、シート内のすべてのセル（1048576行、16384列）を参照します。

' ワークシートの全セルをクリアする
Cells.Clear

UsedRange

UsedRangeプロパティは、指定したシートで使用している全てのセルを含む長方形の範囲を表します。

Dim ws As Worksheet
Set ws = ActiveSheet

' アクティブシートで、B2が何らかの値を持つ最初のセルで、
' L14が何らかの値を持つ最後のセルである場合は$B$2:$L$14 
Debug.Print ws.UsedRange.Address

CurrentRegion

CurrentRegionプロパティは、参照されているセル/範囲を含む、連続した長方形の範囲を提供します。

Dim myRange As Range

Set myRange = Range("D4:F6")

' D4:F16からB2およびL14まで空白ではないセルが続いている場合
' $B$2:$L$14を表示する 
Debug.Print myRange.CurrentRegion.Address

' 開始位置は単一セルを指定しても良い

Set myRange = Range("D4") ' $B$2:$L$14を表示する

Rangeプロパティ

以下のように、アドレス、セルの行／列番号、範囲内の行／列数を取得することができます。

Dim myRange As Range

Set myRange = Range("A1:F10")

' $A$1:$F$10を表示する
Debug.Print myRange.Address

Set myRange = Range("F10")

' 10行目を10と表示する
Debug.Print myRange.Row

' F列の6を表示する
Debug.Print myRange.Column

Set myRange = Range("E1:F5")
' 範囲内の行数を5と表示する
Debug.Print myRange.Rows.Count

' 範囲内の列の数を 2 と表示する
Debug.Print myRange.Columns.Count

シートの最後のセル

Rows.Countと Columns.CountのプロパティをCellsオブジェクトに設定することで、シート上の最後のセルを取得することができます。

' 最後の行番号(=1048576)を表示する
Debug.Print "シート内の行数: " & Rows.Count

' 最後の列番号(=16384)を表示する
Debug.Print "シート内の列数: " & Columns.Count

' 最後のセルのアドレスを表示する
' Prints $XFD$1048576
Debug.Print "シート内の最後のセルのアドレス: " & Cells(Rows.Count, Columns.Count).Address

列の最後に使用された行番号

ENDプロパティは範囲の最後のセルを取得し、End(xlUp)はそのセルから最初に使用されたセルまで取得します。

Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

行内で最後に使用された列番号

Dim lastCol As Long

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

ENDプロパティは範囲の最後のセルを取り、End(xlToLeft)はそのセルから最初に使われたセルまで左に移動します。 また、xlDownとxlToRightプロパティを使用すると、現在のセルから最初に使用された下または右のセルに移動することができます。

セルのプロパティ

共通プロパティ

以下は、よく使われるセルプロパティを表示するコードです。

Dim cell As Range
Set cell = Range("A1")

cell.Activate
Debug.Print cell.Address
' $A$1

Debug.Print cell.Value
' セルの値、例えば456が表示される

Debug.Print cell.Formula
' セルの数式、例えば =SUM(C2:C3) が表示される

' コメント
Debug.Print cell.Comment.Text

' スタイル
Debug.Print cell.Style

' セルフォーマット
Debug.Print cell.DisplayFormat.NumberFormat

セルフォント

Cell.Fontオブジェクトは、セルフォントのプロパティを含んでいます。

Dim cell As Range

Set cell = Range("A1")

' Bold Italicを設定する
cell.Font.FontStyle = "Bold Italis"
' 以下も同じ
cell.Font.Bold = True
cell.Font.Italic = True

' フォントをCourierに設定する
cell.Font.FontStyle = "Courier"

' フォントの色を設定する
cell.Font.Color = vbBlue
' または
cell.Font.Color = RGB(255, 0, 0)

' フォントサイズを設定する
cell.Font.Size = 20

コピー＆ペースト

すべて貼り付け

範囲/セルは、ある場所から別の場所へコピー＆ペーストすることができます。次のコードは、コピー元の範囲のすべてのプロパティをコピー先の範囲にコピーします（CTRL-CとCTRL-Vに相当します）。

'単純コピー
Range("A1:D20").Copy 
Worksheets("Sheet2").Range("B10").Paste

'または
'現在のシートから 'Sheet2' という名前のシートにコピーする
Range("A1:D20").Copy destination:=Worksheets("Sheet2").Range("B10")

形式を選択して貼り付け

PASTESPECIALオプションを使用すると、コピーしたいプロパティを選択して貼り付けることができます。

' 値としてのみ範囲を貼り付ける
Range("A1:D20").Copy
Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues

以下は、貼り付けオプションで可能なオプションです。

' 選択可能なオプション
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPaste
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

オートフィット

内容に合わせて行や列のサイズを変更できるオートフィット機能。

' 内容に合わせて1〜5行目の大きさを変更する 
Rows("1:5").AutoFit

' コンテンツに合わせてA列からB列の大きさを変更する 
Columns("A:B").AutoFit

その他のレンジの例

GUIで必要なアクションを実行しながら、マクロの記録を使用することをお勧めします。様々なオプションとその使い方を理解するのに役立ちます。

For Each

For Eachを使えば、簡単にセルの範囲をループさせることができます。

For Each cell In Range("A1:B100")
    ' セルに対する処理
Next cell

ループの各反復で、範囲内の1つのセルが変数cellに代入され、そのセルに対してForループ内の文が実行されます。すべてのセルが処理されるとループは終了します。

Sort

Sortは、Rangeオブジェクトのメソッドです。Range.Sortにオプションを指定することで、範囲をソートすることができます。以下のコードは、セルC2のキーに基づいて、A:Cの列をソートします。ソート順は、xlAscending（昇順）とxlDescending（降順）があります。Header:= xlYes は、最初の行がヘッダー行である場合に使用します。

  Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Find

FindもRangeオブジェクトのメソッドです。検索条件に一致する内容を持つ最初のセルを見つけ、そのセルをRangeオブジェクトとして返します。一致するものがない場合はNothingを返します。

次の（前の）該当するセルを見つけるには、FindNextメソッド（またはFindPreviousメソッド）を使用します。 次のコードは、範囲内の 「John」で始まるすべてのセルのフォントを 「Arial Black」に変更します。

For Each c In Range("A1:A100")
    If c Like "John*" Then
        c.Font.Name = "Arial Black"
    End If
Next c

次のコードは、指定された範囲内の「To Test」を「Passed」に置き換えます。

With Range("a1:a500")
    Set c = .Find("To Test", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            c.Value = "Passed"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With

注意すべきは、FindNextを使うには範囲を指定しなければならないことです。また、停止条件を指定しないと永久にループが実行されます。通常、最初に見つかったセルのアドレスは変数に格納され、そのセルに再び到達したときにループが停止します。また、何も見つからなかったときにもループを停止させる必要があります。

範囲のアドレス

Range.Addressを使って、A1スタイルのアドレスを取得します。

MsgBox Range("A1:D10").Address
'または
Debug.Print Range("A1:D10").Address

xlReferenceStyle (デフォルトは xlA1) を使用して、R1C1 スタイルのアドレスを取得します。

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
'または
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) 

変数に格納された範囲を扱うときに、特定のアドレスに対してのみ処理を行いたい場合に有効です。

セルの範囲を配列へ

範囲を配列に移し、その値を処理する方が高速で簡単です。配列に範囲を入れるために必要なサイズを計算しないで済むように、配列をVariantとして宣言しておくのが良いでしょう。配列の寸法は、範囲内の値の数と一致するように設定されます。

Dim DirArray As Variant
' 範囲内の値をArrayに格納する

DirArray = Range("a1:a5").Value

' ループで値を処理する
For Each c In DirArray
    Debug.Print c
Next

配列からセルの範囲へ

処理後、ArrayはRangeに書き戻すことができます。上の例のArrayをRangeに書き込むには、Arrayの要素数と同じ大きさのRangeを指定する必要があります。 以下のコードで、ArrayをD1:D5の範囲に書き込みます。

Range("D1:D5").Value = DirArray 

Range("D1:H1").Value = Application.Transpose(DirArray)

行に書き込む場合は、配列の転置が必要なことに注意してください。

範囲の和をとる

SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print SumOfRange

上記の例のように、関数名の前にApplication.WorkSheetFunction を指定することで、Excel で利用できる多くの関数を VBA コードで使用することができます。

範囲を数える

' 範囲内の数値のあるセルの数を数える
CountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print CountOfCells

' 範囲内の空白でないセルの数を数える
CountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print CountOfNonBlankCells

書いた人: Vinamra Chandra