Excel VBA テーブル関連の良く使うメソッド集（ListObject）

テーブルを操作するのVBA

VBAでテーブルのオブジェクトを操作するにはWorksheetオブジェクトメンバのListobjectオブジェクトにアクセスすることになります。

下記によく使うメソッドとプロパティを例文を含めて紹介していきます。コード集です。この記事は解説を入れてリライトする予定です。

テーブル化する

ActiveSheet.ListObjects.Add xlSrcRange, Selection.CurrentRegion

見出し行(ヘッダー行)を取得する

Dim HeaderRowNum As Long
Dim HeaderRow As Range
HeaderRowNum = ActiveSheet.ListObjects(1).HeaderRowRange.Row '行番号
Set HeaderRow = ActiveSheet.ListObjects(1).HeaderRowRange 'Rangeオブジェクト

列数を取得する

Dim ColsCnt As Long
ColsCnt = ActiveSheet.ListObjects(1).Listcolumns.Count

行数（レコード数）を取得する

ActiveSheet.ListObjects(1).Listrows.Count

テーブルのデータ範囲を全選択する（ヘッダーを含めない）

ActiveSheet.ListObjects(1).DataBodyRange.Select

テーブルのデータ範囲を全選択する（ヘッダーを含める）

ActiveSheet.ListObjects(1).Range.Select

1件目のレコードの1列目のアドレスを取得する

'1列目
ActiveSheet.ListObjects(1).ListRows(1).Range(1).Address
'3列目
ActiveSheet.ListObjects(1).ListRows(1).Range(3).Address

Dim MyAddress as String
MyAddress = ActiveSheet.ListObjects(1).DataBodyRange(1,1).Address

Dim MyRow , MyCol as Long
Dim MyAddress as String
MyRow = ActiveSheet.ListObjects(1).DataBodyRange.Row
MyCol = ActiveSheet.ListObjects(1).DataBodyRange.Column
MyAddress = ActiveSheet.Cells(MyRow , MyCol).Address

レコード（行）の選択

'3行目を選択する。
ActiveSheet.ListObjects(1).ListRows(1).Select

最終行番号を取得する（テーブルとして定義している領域の最終行）

下記のように求めます。
– 見出し行番号（テーブルの開始行番号）+ レコード数（テーブルの行数）

Dim StartRow , RowsCnt , EndRow As Long
'見出し行番号（テーブルの開始行番号）
StartRow = ActiveSheet.ListObjects(1).HeaderRowRange.Row
'レコード数（テーブルの行数）
RowsCnt = ActiveSheet.ListObjects(1).ListRows.Count
'最終行番号
EndRow = StartRow + RowsCnt

最終行番号を取得する（実際にデータが入っている最終行）

ListRows.Countではテーブルとして定義されている領域の最終行が取得される。
つまり、実際には3行目までしかデータが入っていなくても、10行目までテーブルが定義してあると、RowsCnt=10となってしまうのです。RowsCnt=3を取得したい場合はこちらです。

Dim EndRow As Long
'最終行番号
EndRow = st.Cells(1, 1).ListObject.Range.Columns(1).Cells.Find("*", SearchDirection:=xlPrevious).Row

最終列番号を取得する

下記のように求めます。
– １列目の列番号（テーブルの開始列番号） + カラム数（テーブルの列数） – 1

Dim StartCol , ColsCnt , EndCol As Long
'１列目の列番号（テーブルの開始列番号）
StartCol = ActiveSheet.ListObjects(1).HeaderRowRange.Column
'カラム数（テーブルの列数）
ColsCnt = ActiveSheet.ListObjects(1).ListColumns.Count
'最終列番号
EndCol = StartCol + ColsCnt - 1

シート内にテーブルが存在するかを判定する

上記のようにListobjectオブジェクトを使ってコードを書く場合は、テーブルが存在しない場合も考慮する必要があるかもしれません。シート内のテーブルの有無をチェックして、テーブルが存在しない場合にプログラムを終了する例を紹介します。

If ActiveSheet.ListObjects.Count = 0 Then Exit Sub

テーブルに名前を付ける

シート内で複数のテーブルを作成することはあまりお勧めできませんが、複数のテーブルを作成する場合は名前を付けると便利です。

ActiveSheet.ListObjects(1).Name = "MyFirstTable"
Msgbox ("ぼくのはじめてのテーブルの名前は " & ActiveSheet.ListObjects(1).Name & "だよ。")

テーブル範囲のリサイズ

カレントリージョンの場合

ActiveSheet.ListObjects(1).Resize Selection.CurrentRegion

テーブルに行・列を追加

'行末に行の追加　
ActiveSheet.range("A1").ListObject.Listrows.add
'2列目に列の追加 Positionオプションで場所指定
ActiveSheet.range("A1").ListObject.ListColumns.add Position:=2

列名の取得と列名の変更

with ActiveSheet.range("A3").ListObject
    '最後列の列名を取得する
    msgbox .ListColumns(.ListColumns.count).name
    '最後列の列名を変更する
    .ListColumns(.ListColumns.count).name = "Product Name"
End with

集計行の表示

追加ではなく表示・非表示という表現をします。

ActiveSheet.ListObjects(1).ShowTotals = True

集計行の取得

Dim Rng as Range
Set Rng = ActiveSheet.ListObjects(1).TotalsRowRange

テーブルスタイルを変更する

'テーブルスタイル名は文字列で指定します。
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight3"

'テーブルスタイルを削除する（装飾をすべてなくす）には空の文字列を渡します。
ActiveSheet.ListObjects(1).TableStyle = ""

テーブルを範囲に変換（テーブルを解除）

いきなり変換すると書式が残ってしまうので、テーブルスタイルを削除してから変換すべし。

ActiveSheet.ListObjects(1).ListObject.TableStyle = ""
ActiveSheet.ListObjects(1).ListObject.Unlist

行の挿入（レコードの追加）

'最終行に行挿入
ActiveSheet.ListObjects(1).ListRows.Add
'テーブルの3行目に行挿入
ActiveSheet.ListObjects(1).ListRows.Add(3)

ちなみにAddメソッドにはAlwaysInsertというオプショナルな引数もありますが、私は使ったことがないのでここでの説明は省略します。

行・列単位のデータクリア

with ActiveSheet
   3列目のデータをまるごと空にする
   .Cells(1, 1).ListObject.ListColumns(3).DataBodyRange.ClearContents
end with

行の全件削除（レコード全件削除）

下記のいづれか

ActiveSheet.ListObjects(1).DataBodyRange.Delete
ActiveSheet.ListObjects(1).Range.Delete

テーブル範囲が列見出し行とデータボディレンジ1行の2行だけになります。（集計行を使っている場合は集計行も表示されます）中のデータは空っぽになります。

おわり

以上、テーブル関連（ListObject）のVBAのメソッドについてコード集でした。