ExcelのVBAでテーブルの範囲を指定する方法｜ListObjectのDataBodyRange・列名で参照して行の増減に強くする

VBAで表を扱うとき、Range("A2:D100") のようにセル番地を固定したり、End(xlUp) で最終行を探したりすると、行の追加・削除や列の入れ替えでコードが壊れます。対象をExcelのテーブルにしておけば、VBAからはテーブル（ListObject）として扱えます。範囲も「データ部分」「金額の列」のように名前で指定できます。

この記事では、次のテーブルを例に説明します。

• シート名：売上
• テーブル名：T_売上
• 列：日付、取引先、金額、状態

セル番地で書いたコードとの違い

セル番地で書くと、金額の合計は次のようになります。

lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
total = WorksheetFunction.Sum(ws.Range("C2:C" & lastRow))

金額の列がC列から別の列に移ったり、表の下にメモを書いたりすると、意図しない範囲を合計します。テーブルを使うと、次のように書けます。

total = WorksheetFunction.Sum(lo.ListColumns("金額").DataBodyRange)

列の位置や行数が変わっても、「T_売上の金額列のデータ部分」を指すことは変わりません。

テーブル名を確認してListObjectを取得する

テーブル内のセルを選ぶと、「テーブルデザイン」タブが表示されます（バージョンによって表示名が異なります）。その左端で、テーブル名を確認・変更できます。初期値の「テーブル1」のままだと何の表か分かりにくいので、用途の分かる名前に変えておきます。

Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets("売上").ListObjects("T_売上")

ListObjects(1) のように番号で指定すると、同じシートにテーブルを追加したときに別のテーブルを指すことがあります。テーブルは名前で指定します。

シート名が変わる可能性がある場合は、ブック内の全シートからテーブル名で探す関数を用意する方法もあります。テーブル名はブック内で重複しないため、名前だけで特定できます。

Function GetTable(wb As Workbook, tableName As String) As ListObject
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        On Error Resume Next
        Set GetTable = ws.ListObjects(tableName)
        On Error GoTo 0
        If Not GetTable Is Nothing Then Exit Function
    Next ws
End Function

見つからなかった場合は Nothing が返ります。呼び出し側で If lo Is Nothing Then を確認してから先へ進みます。

範囲を指定するプロパティ

実際にどの範囲を指しているか迷ったら、ステップ実行（F8）でコードを止めます。その状態でイミディエイトウィンドウに ?lo.DataBodyRange.Address と入力すると、範囲のアドレスを確認できます。

列名で指定して値を読み書きする

1行ずつ処理する

DataBodyRange.Cells(行, 列) の行と列は、テーブルのデータ部分の左上を1行1列目とした位置です。列の位置は ListColumns("列名").Index で取得します。

Dim i As Long
Dim colAmount As Long, colStatus As Long
colAmount = lo.ListColumns("金額").Index
colStatus = lo.ListColumns("状態").Index

For i = 1 To lo.ListRows.Count
    If lo.DataBodyRange.Cells(i, colAmount).Value >= 100000 Then
        lo.DataBodyRange.Cells(i, colStatus).Value = "要確認"
    End If
Next i

行数が多いときは列ごとに配列で読み書きする

行数が多い場合は、セルを1つずつ読み書きするより、列単位で配列に読み込むほうが速く処理できます。

Dim vAmount As Variant, vStatus As Variant
vAmount = ToArray2D(lo.ListColumns("金額").DataBodyRange)
vStatus = ToArray2D(lo.ListColumns("状態").DataBodyRange)

For i = 1 To UBound(vAmount, 1)
    If IsNumeric(vAmount(i, 1)) Then
        If vAmount(i, 1) >= 100000 Then vStatus(i, 1) = "要確認"
    End If
Next i

lo.ListColumns("状態").DataBodyRange.Value = vStatus

書き戻すのは、変更した「状態」の列だけにします。lo.DataBodyRange.Value = 配列 のように表全体を書き戻すと、数式の入った列まで値で上書きされます。

データが1行だけのとき、1セルの .Value は配列ではなく単一の値になります。そのまま UBound を使うとエラーになるため、次の関数で常に2次元配列に変換しています。

Function ToArray2D(rng As Range) As Variant
    Dim a(1 To 1, 1 To 1) As Variant
    If rng.Cells.CountLarge = 1 Then
        a(1, 1) = rng.Value
        ToArray2D = a
    Else
        ToArray2D = rng.Value
    End If
End Function

データが0行のときのエラーを避ける

データが0行のテーブルでは、DataBodyRange は Nothing になります。その状態で .Value や .Cells を使うと、実行時エラー91（オブジェクト変数または With ブロック変数が設定されていません）で止まります。範囲を使う前に行数を確認します。

If lo.ListRows.Count = 0 Then
    MsgBox "T_売上 にデータがありません。", vbInformation
    Exit Sub
End If

セルの内容を消しただけで行を削除していない場合、見た目は空でも ListRows.Count は0になりません。値が入っているかを判定したいときは、WorksheetFunction.CountA(lo.DataBodyRange) = 0 で確認します。

新しいデータを入れる前に、既存のデータ行をすべて削除する場合も、Nothing の確認を先にします。

If Not lo.DataBodyRange Is Nothing Then
    lo.DataBodyRange.Delete
End If

テーブルの右側や下に別のデータを置いている場合は、削除後に配置がずれていないかも確認してください。

行を追加して列名で書き込む

ListRows.Add は、テーブルの末尾に行を1行追加し、その行（ListRow）を返します。データが0行のテーブルにも使えます。計算列を設定している列には、数式が自動で入ります。

Dim r As ListRow
Set r = lo.ListRows.Add
r.Range(1, lo.ListColumns("日付").Index).Value = Date
r.Range(1, lo.ListColumns("取引先").Index).Value = "A商事"
r.Range(1, lo.ListColumns("金額").Index).Value = 12000

列名が見つからないときは先に止める

存在しない列名を ListColumns("金額") で指定すると、実行時エラー9（インデックスが有効範囲にありません）になります。処理の途中で止まらないよう、必要な列があるかを最初に確認します。

Function HasColumn(lo As ListObject, colName As String) As Boolean
    Dim lc As ListColumn
    For Each lc In lo.ListColumns
        If lc.Name = colName Then
            HasColumn = True
            Exit Function
        End If
    Next lc
End Function

Dim colName As Variant
For Each colName In Array("日付", "取引先", "金額", "状態")
    If Not HasColumn(lo, CStr(colName)) Then
        MsgBox "T_売上 に列「" & colName & "」がありません。", vbExclamation
        Exit Sub
    End If
Next colName

全角・半角の違いや、見出しの前後に入った空白でも一致しなくなります。見出しを変えるときは、コード側の列名も合わせて直します。

フィルターで表示中の行だけを扱う

DataBodyRange には、フィルターで非表示になっている行も含まれます。表示中の行だけを処理したい場合は SpecialCells(xlCellTypeVisible) を使います。表示中の行がない場合はエラーになるため、エラーを受け止めてから Nothing かどうかを判定します。

Dim vis As Range, c As Range
On Error Resume Next
Set vis = lo.ListColumns("金額").DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If vis Is Nothing Then
    MsgBox "表示中の行がありません。", vbInformation
    Exit Sub
End If

For Each c In vis.Cells
    ' c.Row はシートの行番号
    Debug.Print c.Row, c.Value
Next c

表示中のセルは、離れた複数の範囲に分かれることがあります。.Value で配列にまとめて読むと一部しか取れないため、For Each で1セルずつ処理します。

構造化参照の文字列で指定する方法

ワークシートの数式と同じ構造化参照の書き方で、範囲を指定することもできます。

With ThisWorkbook.Worksheets("売上")
    Debug.Print .Range("T_売上[金額]").Address      ' 金額列のデータ部分
    Debug.Print .Range("T_売上[#All]").Address      ' 見出し＋データ全体
    Debug.Print .Range("T_売上[#Headers]").Address  ' 見出し行
End With

短く書けますが、テーブル名や列名が文字列の中にあるため、名前の誤りは実行するまで分かりません。また、ブックやシートを修飾せずに書くと、そのとき選ばれているブックやシートによって結果が変わることがあります。列の有無の確認やデータ0行の判定まで含めて書く場合は、ListObject を経由するほうが扱いやすくなります。

動作を確かめるときのポイント

• テーブルのデータが0行・1行・複数行の3つの状態で実行し、エラーにならないかを確かめる
• フィルターをかけた状態でも実行し、非表示の行を処理してよいかを確かめる
• ステップ実行（F8）で止め、DataBodyRange.Address が想定した範囲かを確かめる
• 本番のブックではなく、コピーしたブックで先に試す