EB000005_VBAエキスパート（ExcelVBAベーシック）合格講座／第五回：セルや行、列を指定する

VBAエキスパート（ExcelVBAベーシック）合格講座／第五回：セルや行、列を指定する ExcelVBA Basic
  1. このページの内容について
  2. セルを操作するためのオブジェクト
    1. RangeとCells
      1. RangeとCellsとは
      2. RangeとCellsの違い
    2. セルの指定方法
      1. Cells
        1. 書式
        2. サンプルコード（クリックでコピー）
        3. 実行結果
      2. Range
        1. 書式
        2. サンプルコード（クリックでコピー）
        3. 実行結果
  3. RangeやCells以外でセルを指定する方法
    1. ActiveCellとSelection
      1. ActiveCellとSelectionとは
      2. ActiveCell
        1. サンプルコード（クリックでコピー）
        2. 実行結果
      3. Selection
        1. サンプルコード（クリックでコピー）
        2. 実行結果
  4. 行や列を指定するオブジェクト
    1. 行の指定
      1. RowとRows
      2. 書式
      3. サンプルコード（クリックでコピー）
      4. 実行結果
    2. 列の指定
      1. ColumnとColumns
      2. 書式
      3. サンプルコード（クリックでコピー）
      4. 実行結果
    3. 任意のセルを含む行・列
      1. EntireRowとEntireColumn
      2. 書式
      3. サンプルコード（クリックでコピー）
      4. 実行結果
  5. 基準となるセルから他のセルを参照する
    1. Offset
      1. 基準となるセルから相対的に指定した位置にあるセルを表す
      2. 書式
      3. 単一のセルを操作するサンプル
        1. サンプルコード（クリックでコピー）
        2. 実行結果
      4. 範囲のセルを操作するサンプル
        1. サンプルコード（クリックでコピー）
        2. 実行結果
    2. Resize
      1. 基準となるセルの範囲を変更したセル範囲を表す
      2. 書式
      3. 単一のセルを操作するサンプル
        1. サンプルコード（クリックでコピー）
        2. 実行結果
      4. 範囲のセルを操作するサンプル
        1. サンプルコード（クリックでコピー）
        2. 実行結果
    3. End
      1. 連続したデータが入力されている最終のセルを表す
      2. 書式
      3. 指定できる定数
      4. サンプルコード（クリックでコピー）
        1. 実行結果
    4. CurrentRegion
      1. 基準となるセルが含まれている表全体のセル範囲を表す
      2. CurrentRegionが示す範囲
      3. 書式
      4. サンプルコード（クリックでコピー）
        1. 実行結果
    5. 番外編：Range
      1. 他のセルを参照するプロパティの重ね掛け
      2. サンプルコード（クリックでコピー）
      3. 実行結果
  6. VBAエキスパート試験対策記事

このページの内容について

このページは、VBAエキスパート（ExcelVBAベーシック）試験合格講座の第五回記事です。第公式テキスト第6章「セルの操作」の内容について解説します。1時間の講義でお話する程度の分量です。講座受講者の復習での利用を想定しています。

セルを操作するためのオブジェクト

RangeとCells

RangeとCellsとは

RangeとCellsはExcelVBA（マクロ）でセルを操作するためのオブジェクトです。どちらもコレクションやオブジェクトのような動きをしますが厳密には別のもので、通常のコレクションやオブジェクトとは少し異なる指定の仕方をします。

RangeとCellsの違い

RangeとCellsはそれぞれほとんど同じプロパティやメソッドを持っていますが、Rangeはセルを範囲で扱うことができるのに対し、Cellsは単一のセルのみ扱うことができます。Rangeは単一のセルを範囲として扱うこともきるため、Rangeの方が優れているようにも見えます。しかしセルを指定する方法がそれぞれ異なるため、場面に応じて使いやすい方・読みやすい方を使用します。

セルの指定方法

Cells

Cellsはセルの行と列の位置を指定して操作をすることができます。どちらが行と列かは下記画像を参照してください。行と列は整数値で指定します。マイナス値を指定することはできません。行と列の間は「,（カンマ）」で区切り、それぞれ省略することはできません。また、列方向の指定については「”A”」や「”C”」のように列を表す文字列を指定することができます。

書式
Cells(行,列)
サンプルコード（クリックでコピー）
Private Sub Test_Cells()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    ws.Cells(2, 3).Value = 10000    'C2
    ws.Cells(1,"D").Value = 20000   'D1
    
End Sub

このサンプルではワークシートを特定していますが特定せずに実行することもできます。その場合、マクロ実行時にアクティブになっているシートの「C2（2行目,3列目）」に対する操作とみなされます。

実行結果

上記サンプルコードを実行すると、マクロを実行中のブックの1番目のシートのセル「C2」に値「10000」が、セル「D1」に値「20000」が入力されます。

Valueプロパティを書き換えることでセルに格納されている内容が書き換えられます。次回記事で改めて紹介します。

Range

Rangeでセルを指定する方法は、２つあります。1つはセルの範囲を表す文字列で指定する方法、もう１つは範囲の左上のセルと右下のセルを指定する方法です。

書式
Range(範囲指定文字列)
Range(左上のセル,右上のセル)
サンプルコード（クリックでコピー）
Private Sub Test_Range()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    '■範囲指定文字列
    ws.Range("A2").Value = 10000    '①→A2
    ws.Range("D1,E3").Value = 20000 '②→D1とE3
    ws.Range("B1:C2").Value = 30000 '③→B1からC2の範囲
    
    '■左上のセルと右下のセル
    ws.Range(ws.Cells(4, 1), ws.Cells(5, 3)).Value = 40000  '④A4からC5の範囲
    ws.Range(ws.Range("A7"), ws.Range("C7")).Value = 50000  '⑤A6からC7の範囲
    
End Sub
実行結果

上記サンプルを実行すると、以下のような処理が実行されます。
①セル「A2」に対して値「10000」を入力
②セル「D1」とセル「E3」にぞれぞれ値「20000」を入力　※範囲ではない。
③セル「B1」からセル「C2」のセル範囲に値「30000」を入力
④セル「A4（4,1）」からセル「C5（5,3）」のセル範囲に値「40000」を入力
⑤セル「A7」からセル「C7」のセル範囲に値「50000」を入力

RangeやCells以外でセルを指定する方法

ActiveCellとSelection

ActiveCellとSelectionとは

セルを操作するオブジェクトはRangeとCellsだけではありません。ActiveCellとSelectionは位置を座標で指定するのではなく、Excel上での選択状態に応じてセルを指定することができます。

ActiveCell

公式テキストではActiveCell（アクティブセル）の説明に「キーを打ったとき、それが入力されるセル」と表現していますが、少し実態とは異なります。例えば挿入した画像を選択している状態でキー入力をしてもどこにも入力内容は反映しません。しかし、この状態でもアクティブセルは存在しており、それは画像を選択する前に選択していたセルです。そのためこれを言い換えて「最後に選択していたセル」と表現するのが正解であるように思います。

別の記事の説明ですが、アクティブセルが属するシートをアクティブシート、アクティブシートが属するブックをアクティブブックと呼びます。

サンプルコード（クリックでコピー）
Private Sub Test_ActiveCell()

    Debug.Print Replace(ActiveCell.Address, "$", "")

End Sub

ActiveCellではセル範囲を取得することはできません。セル範囲を選択して実行した場合は、左上のセルを操作対象と認識します。

実行結果

上記サンプルを実行すると「最後に選択されていた」セルのアドレスをイミディエイトウィンドウに表示します。RangeのAddressプロパティを使用しています。

Selection

Selectionは選択しているもののオブジェクトを取得します。対象はセルのみではなく、グラフや図形が選択されている場合はグラフや図形のオブジェクトとして振舞います。ActiveCellとは異なり、セル範囲を選択している場合は、セル範囲を操作対象とします。

サンプルコード（クリックでコピー）
Private Sub Test_Selection()

    Debug.Print TypeName(Selection)
    
End Sub
実行結果

サンプルコードを実行すると選択中のオブジェクトの型名をイミディエイトウィンドウに表示します。Selectionを使用する場合、マクロの動作がユーザーの操作に大きく影響を受けるため、何を選択しているかをTypeNameで確かめてから実行するのが基本です。

行や列を指定するオブジェクト

行の指定

RowとRows

Excelの１行はRowで表されます。Rowの集合体（コレクション）がRowsです。行番号を整数値で指定してRowを特定します。また、複数行を表すために範囲指定文字列を使用することもできます。さらに範囲指定文字列を使用することもでき、さらに範囲指定文字列を使用する場合、Rangeを使っても同じように指定できます。

書式

Rows(行番号)
Rows(範囲指定文字列)
Range(範囲指定文字列)

サンプルコード（クリックでコピー）

Private Sub Test_RowsSingle()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Rows(4).Insert  '①
    
End Sub

Private Sub Test_RowsMulti()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Rows("4:8").Insert  '②
    
End Sub

Private Sub Test_RowsRangeSingle()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Range("6:6").Insert  '③
    
End Sub

Private Sub Test_RowsRangeMulti()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Range("6:8").Insert  '④
    
End Sub

実行結果

上記サンプルコードはそれぞれ、指定された行で「（行の）挿入」をします。
　①4行目
　②4～8行目
　③6行目　　　※Range(“6”)とは書けません。
　④6～8行目

RowsとRangeとそれぞれで行を指定する場合、まったく同じ動作を再現しているように見えますが、Rangeは行に含まれるすべてのセルを表し、Rowsは行全体を表しています。そのため使用できるプロパティやメソッドが若干異なります。特に理由がない限り、行の指定にはRowsを使用しましょう。

列の指定

ColumnとColumns

Excelの１列はColumn（カラム）で表されます。Columnの集合体（コレクション）がColumnsです。列番号を整数値で指定するか、列を合わらずアルファベット文字列でColumnを特定します。また、複数行を表すために範囲指定文字列を使用することもでき、さらに範囲指定文字列を使用する場合、Rangeを使っても同じように指定できます。

書式

Columns(行番号)
Columns(行指定文字列)
Columns(範囲指定文字列)
Range(範囲指定文字列)

サンプルコード（クリックでコピー）

Private Sub Test_ColumnsSingle()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Columns(2).Insert  '①
    
End Sub

Private Sub Test_ColumnsSingleAlphabet()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Columns("B").Insert  '②
    
End Sub

Private Sub Test_ColumnsMulti()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Columns("B:D").Insert  '③
    
End Sub

Private Sub Test_ColumnsRangeSingle()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Range("A:A").Insert  '④
    
End Sub

Private Sub Test_ColumnsRangeMulti()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Call ws.Range("A:B").Insert  '⑤
    
End Sub

実行結果

上記サンプルコードはそれぞれ、指定された列で「（列の）挿入」をします。
　①2列目（B列）
　②B列
　③B:D列　　　※Columns(“2:4”)とは書けません。
　④A列　　　 ※Range(“A”)とは書けません。
　⑤A:B列

任意のセルを含む行・列

EntireRowとEntireColumn

基準となるセルに含まれる行全体や列全体を表すのが「EntireRow」と「EntireColumn」です。

書式

セルを表すオブジェクト.EntireRow
セルを表すオブジェクト.EntireColumn

サンプルコード（クリックでコピー）

Private Sub Test_EntireRowSingle()
    
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).Range("A4")
    rng.EntireRow.Insert  '①

End Sub
Private Sub Test_EntireRowMulti()
    
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).Range("A4:B5")
    rng.EntireRow.Insert  '②

End Sub

Private Sub Test_EntireColumnSingle()
    
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).Range("A4")
    rng.EntireColumn.Insert  '③

End Sub
Private Sub Test_EntireColumnMulti()
    
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).Range("A4:B5")
    rng.EntireColumn.Insert  '④

End Sub

実行結果

上記サンプルコードはそれぞれ、指定された行や列で「（行や列の）挿入」をします。
　①セル「A4」が含まる４行目全体
　②セル範囲「A4:B5」が含まれる4～5行目全体
　③セル「A4」が含まれるA列全体
　④セル範囲「A4:B5」が含まれるA～B列全体

基準となるセルから他のセルを参照する

Offset

基準となるセルから相対的に指定した位置にあるセルを表す

位置の指定方法はCellsのように行と列をカンマで区切った数値で指定します。Cellsと異なり、シートの範囲から逸脱しなければマイナス値を指定することもできます。

RangeやCellsが通常のオブジェクトやコレクションとは異なる性質を持っているのと同じように、Offsetプロパティも通常のプロパティはとは異なりメソッドのような性質を持った特殊なプロパティです。ExcelVBA全般に言えることですが、セルを指定するための一連のオブジェクトは他のVBAにはないExcelVBA独自のオブジェクトであり、他にも例外的な処理ができるプロパティやメソッドが多く用意されています。

書式

セルを表すオブジェクト.Offset(行,列)

単一のセルを操作するサンプル

サンプルコード（クリックでコピー）
Private Sub Test_Offset()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("C5")
    
    rng.Offset(1, 1).Value = "D6"    '①
    rng.Offset(-4, -2).Value = "A1"  '②
    
End Sub
実行結果

上記サンプルコードではセル「C5」を起点として
　①＋1行＋１列の位置のセル「D6」
　②－4行－２列の位置のセル「A1」
を参照して、値を入力します。

範囲のセルを操作するサンプル

Offsetプロパティはセル範囲に対しても有効です。範囲のサイズを変えずにそのまま位置を移動したセル範囲を表します。

サンプルコード（クリックでコピー）
Private Sub Test_OffsetRange()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("C5:D6")
    
    rng.Offset(1, 1).Value = "D6:E7"    '①
    rng.Offset(-4, -2).Value = "A1:B2"  '②
    
End Sub
実行結果

上記サンプルコードではセル範囲「C5:D6」を起点として
　①＋1行＋１列の位置のセル範囲「D6:E7」
　②－4行－２列の位置のセル範囲「A1:B2」
を参照して、値を入力します。

Resize

基準となるセルの範囲を変更したセル範囲を表す

セルの範囲の変更はOffsetと同様変更するサイズを行、列で指定します。Offsetとは異なり0やマイナス値を指定することはできません。

書式

セルを表すオブジェクト.Resize(行,列)

単一のセルを操作するサンプル

サンプルコード（クリックでコピー）
Private Sub Test_Resize()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("C5")
    
    rng.Resize(2, 2).Value = "C5:D6"

End Sub
実行結果

上記サンプルコードではセル「C5」を起点として、2×2のセル範囲を参照して、値を入力します。

Resize(2,2)は、行・列を２ずつ増やすのではなく、2×2の範囲にすることに注意してください。

範囲のセルを操作するサンプル

Resizeプロパティはセル範囲に対しても有効ですが、挙動には注意が必要です。

サンプルコード（クリックでコピー）
Private Sub Test_ResizeRange()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("A1:B2") '①
    rng.Resize(1, 1).Value = "A1"
    
    Set rng = ThisWorkbook.Worksheets(1).Range("C3:D4") '②
    rng.Resize(2, 2).Value = "C3:D4"
    
    Set rng = ThisWorkbook.Worksheets(1).Range("E5:F6") '③
    rng.Resize(3, 3).Value = "E5:G7"
    

End Sub
実行結果

上記サンプルコードでは例えば①はセル範囲「A1:B2」を起点としているように見えますが、実際にはセル範囲ではなく、セル範囲の左上のセル「A1」を起点としています。そのため、
　①セル「A1」を起点として1×1のセル範囲「A1」
　②セル「C3」を起点として2×2のセル範囲「C3:D4」
　③セル「E5」を起点として3×3のセル範囲「E5:G7」
を参照して、値を入力します。

End

連続したデータが入力されている最終のセルを表す

Excelで「Ctrl」＋「方向キー」を押したときに到達するセルを表します。方向はVBAで用意されている定数（Excel.XlDirection）で指定します。

書式

セルを表すオブジェクト.End(XlDirection)

指定できる定数

方向キー方向を表す定数（XlDirection）
↑xlUp
↓xlDown
←xlToLeft
→xlToRight

サンプルコード（クリックでコピー）

Private Sub Test_End()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("A1")
    rng.Value = 100
    
    Set rng = ThisWorkbook.Worksheets(1).Range("A2")
    rng.Value = 200
    
    Set rng = ThisWorkbook.Worksheets(1).Range("A3")
    rng.Value = 300
    
    Debug.Print rng.End(xlUp)
    
End Sub
実行結果

上記サンプルコードではセル「A1」「A2」「A3」へ値を入力し、セル「A3」を起点として「Ctrl」＋「↑」を押したときに移動する先のセルに格納されている値をイミディエイトウィンドウに出力します。

Endプロパティを実行したとしても、カーソルが移動するわけではありません。

CurrentRegion

基準となるセルが含まれている表全体のセル範囲を表す

Excelで「Ctrl」＋「*（アスタリスク）」を押した時に選択されるセル範囲を表します。セルの範囲に値が入力されている場合に、範囲に含まれるセルを基準にして表全体のセル範囲を取得します。

公式テキストには「Ctrl」＋「Shift」＋「*」と記載がありますが、これはキーボードでは「*」を入力するために「Shift」+「:」と入力する必要があるためかと思います。しかしテンキーで直接「*」を入力できる場合「Ctrl」＋「Shift」＋「*」と入力しても正しく動作はしませんので、正しくは「Ctrl」＋「*」です。

CurrentRegionが示す範囲

下記のような表があるときにセル範囲「A1:C4」の中に含まれる任意のセルを選択した状態とします。その状態で「Ctrl」＋「*」を入力すると、選択範囲が表全体に拡大されます。この「Ctrl」＋「*」を入力するときに選択される範囲がCurrentRegionです。

セル「B3」を選択した状態で「Ctrl」+「*」

セル範囲「A1:C4」が選択される

書式

セルを表すオブジェクト.CurrentRegion

サンプルコード（クリックでコピー）

Private Sub Test_CurrentRegion()

    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("B3")
    rng.CurrentRegion.Value = 100
    
End Sub
実行結果

上記サンプルコードではセル「B3」が含まれる表の範囲に値「100」を入力します。

番外編：Range

他のセルを参照するプロパティの重ね掛け

他のセルへの参照は（OffsetやResize他）は重ね掛けすることができます。Rangeも例外ではなく、Rangeオブジェクト内にはRangeが入れ子で存在しています。

サンプルコード（クリックでコピー）

Private Sub Test_RangeRange()
    
    Dim rng As Range
    
    Set rng = ThisWorkbook.Worksheets(1).Range("B2")
    rng.Range("A1:C3").Value = 1000
    
End Sub

実行結果

上記のサンプルを実行すると、セル「B2」を起点にして3×3マスに値「1000」が入力されます。「”A1:C3″」という表記は一見すると絶対参照のように見えますが、起点となるセルを指定することで「起点と同じ位置から3×3」のセルを表す相対参照として振る舞います。

では「Range(“B2”).Range(“B2”)」はどのセルを指すでしょうか？これは自分自身で考え、検証してみてください。

VBAエキスパート試験対策記事

当サイトでは、オデッセイコミュニケーションズ社が運営する試験であるVBAエキスパート（Excel VBA ベーシック／Excel VBA スタンダード）の出題範囲をベースに用語や各種関数の解説などを行っています。試験合格に向けて必須と言われる公式テキストに沿って解説をしています。受験をするか悩んでいる方、テキストとは別視点の解説を見てみたい方、受験はしないがExcelVBA（マクロ）に興味がある方へ向けた記事です。

Comment