Excel VBAのテーブルを操作するためのオブジェクトは、ListObjectを使用します。
Excel VBA ListObject
ListObjectは、テーブル内のセル範囲を効果的に扱います。
テーブルを特定
Range("A1").ListObject
テーブル特定は、テーブル内であればどこでもいいが、一般的には左上セルを指定します。
Sheets("Sheet1").ListObjects(1) ' 1 はテーブルのインデックス
Sheets("Sheet1").ListObjects("テーブル1") ' "テーブル1" はテーブルの名前
Sheet1 はテーブルが存在するシート名、1 はテーブルのインデックス、`"テーブル1"`はテーブルの名前です。ListObjects コレクションは、特定のシート内のテーブルへの参照を提供します。
※テーブルが存在しない場合やインデックスや名前が間違っている場合、エラーが発生します。
ListObject オブジェクトは、特定のセル範囲を Excel テーブルとして表現します。個々のテーブルを表す単一のテーブルオブジェクトです。テーブル内のデータやテーブルのプロパティにアクセスできます。
ListObjects コレクションは、特定のシート内のすべてのテーブルを含んでいます。すべてのテーブルを表す複数のテーブルオブジェクトのコレクションです。
テーブル内の特定部分を操作
ListObjectの Rangeは、Excel テーブル内の特定の部分、行、列を取得するのに利用します。
Range("A1").ListObject.Range は、テーブル内のすべてのセルを示します。
Range("A1").ListObject.Range(3) は、テーブル内のすべてのセル内で何番目のセルか示します。
※A1からE10にテーブルがある場合、Range("A1").ListObject.Range(3)はC1になる。
DataBodyRangeはテーブル内のデータ本体を取得して操作するために使用します。1行目がタイトルの場合、2行目以降が対象範囲になります。
※A1からE10にテーブルがある場合、Range("A1").ListObject.DataBodyRangeはA2からE10が対象です。Range("A1").ListObject.DataBodyRange(5)はE2になります。
HeaderRowRangeはテーブル内のタイトルになります。
Range("A1").ListObject.HeaderRowRange は、テーブルのタイトル(ヘッダー行)の指定です。
ListColumnはテーブル内の列を表します。
Range("A1").ListObject.ListColumnはそれぞれの列で、Range("A1").ListObject.ListColumnsは列全体です。
Excel テーブル内の単一の列を表します。ListObject内の 1 つの列に対応します。
Excel テーブル内のすべての列をまとめたコレクションです。ListObject内のすべての列にアクセスするコレクションです。
テーブル内の列のインデックスを指定して特定の列の範囲を取得します。
テーブル内の列の名前を指定して特定の列の範囲を取得します。
Range("A1").ListObject.ListColumns(2).DataBodyRange
Range("A1").ListObject.ListColumns("名前").DataBodyRange
Range("A1").ListObject.ListColumns(2).Range
Range("A1").ListObject.ListColumns("名前").Range
ListRowはテーブル内の行を表します。
Range("A1").ListObject.ListRowはそれぞれの行で、Range("A1").ListObject.ListRowsは行全体です。
Excel テーブル内の単一の行を表します。ListObject内の 1 行に対応します。
Excel テーブル内のすべての行をまとめたコレクションです。ListObject内のすべての行にアクセスするコレクションです。
行全体を表します。
特定の行を参照します。この行に含まれるセルの範囲を取得するために .Range を使用します。
Excel VBA 構造化参照
構造化参照を使うと、テーブル内のデータにアクセスできます。
構造化参照の使い方
テーブル名[[特殊項目指定子],[列指定子]]
Range("テーブル1[#Data]")
列を含む列データ
Range("テーブル1[[#All],[列見出し名]]")
列を含まない列データ
Range("テーブル1[[#Data],[列見出し名]]")
構造化参照を使ってテーブル内の行指定はできません。テーブル内の行操作は、ListObjectを使います。
テーブルのデータを操作
Range("A1").ListObject.Range.Autofilter 2,"山本"
Range("A1").ListObject.DataBodyRange.Autofilter 2,"山本"
テーブル内の特定の列をフィルタリングしていますが、1つ目のコードはテーブル全体を対象にフィルタリングを行い、2つ目のコードはデータ部分のみを対象にフィルタリングを行います。
①
Range("A1").ListObject.Range.Autofilter 2,"山本"
Range("A1").ListObject.Range.Copy Sheets("Sheet2").Range("A1")
②
With Range("A1").ListObject.Range
.Autofilter 2,"山本"
.Copy Sheets("Sheet2").Range("A1")
End With
①のコードは、2番目の列で 山本 をフィルタリングして、その結果をSheet2のA1セルから始まる範囲にコピーしています。
②のコードでは、①と同様に列をフィルタリングし、その後 With ステートメントを使って同じ操作を1つのブロックにまとめています。With ステートメントを使うことで、ListObject.Range を複数回書く必要がなくなります。これにより、コードがより簡潔になり、修正が容易になります。
With Range("A1").ListObject.DataBodyRange
.Autofilter 2,"山本"
.Copy Sheets("Sheet2").Range("A1")
End With
テーブルのデータ本体(タイトルを除くデータ部分)の特定の列をフィルタリングし、その結果を別のシートにコピーしています。
Range("テーブル1").AutoFilter 2,"山本"
Range("テーブル1[#All]").Copy Sheets("Sheet2").Range("A1")
テーブル(構造化参照)の特定の列をフィルタリングし、その結果を別のシートにコピーしています。
Range("テーブル1").AutoFilter 2,"山本" は、"テーブル1" という名前のテーブル内の2番目の列で"山本"をフィルタリングする操作を行います。
次の行の Range("テーブル1[#All]").Copy Sheets("Sheet2").Range("A1") は、フィルタリングされた結果(テーブル全体を含む)をSheet2のA1セルから始まる範囲にコピーしています。
タイトル行含まないでコピーする
Range("テーブル1").AutoFilter 2,"山本"
Range("テーブル1[#Data]").Copy Sheets("Sheet2").Range("A1")
テーブル(構造化参照)の特定の列をフィルタリングし、その結果を別のシートにコピーしています。
Range("テーブル1").AutoFilter 2,"山本" は、"テーブル1" という名前のテーブル内の2番目の列で"山本"をフィルタリングする操作を行います。
`Range("テーブル1[#Data]").Copy Sheets("Sheet2").Range("A1") は、フィルタリングされた結果(タイトル行を含まないデータ部分)をSheet2のA1セルから始まる範囲にコピーしています。
With Range("A1").ListObject
.Range.AutoFilter 2,"山本"
.ListColumns(1).Range.Copy Sheets("Sheet2").Range("A1")
.ListColumns(4).Range.Copy Sheets("Sheet2").Range("B1")
End With
特定の列だけをコピーして別のシートに貼り付けるものです。
.Range.AutoFilter 2,"山本" は、「山本」という条件で2番目の列をフィルタリングしています。
.ListColumns(1).Range.Copy Sheets("Sheet2").Range("A1") は、フィルタリングされた結果の1番目の列(`.ListColumns(1)`)をSheet2のA1セルから始まる範囲にコピーしています。
.ListColumns(4).Range.Copy Sheets("Sheet2").Range("B1") は、同様にフィルタリングされた結果の4番目の列(`.ListColumns(4)`)をSheet2のB1セルから始まる範囲にコピーしています。
With Range("A1").ListObject
.Range.AutoFilter 2,"山本"
.ListColumns(3).DaraBodyRange.Font.Bold=True
.Range.AutoFilter 2
End With
特定の条件で列の書式を設定するものです。
.Range.AutoFilter 2,"山本" は、「山本」という条件で2番目の列をフィルタリングしています。
.ListColumns(3).DataBodyRange.Font.Bold=True は、フィルタリングされた結果の3番目の列のデータ部分(.DataBodyRange)のフォントを太字に設定しています。
最後の .Range.AutoFilter 2 は、フィルタをクリアしています。
With Range("A1").ListObject.DataBodyRange
.Range.AutoFilter 2,"山本"
.EntireRow.Delete
.Range.AutoFilter 2
End With
特定の条件に基づいて行を削除するものです。
.Range.AutoFilter 2,"山本" は、「山本」という条件で2番目の列をフィルタリングします。.EntireRow.Delete は、フィルタリングした結果の行を削除します。
Range("テーブル1").AutoFilter 2,"山本"
Range("テーブル1[#Data]").EntireRow.Delete
Range("テーブル1").AutoFilter 2
特定の条件に基づいてデータを削除するものです。
Range("テーブル1").AutoFilter 2,"山本" は、「山本」という条件で2番目の列をフィルタリングしています。
Range("テーブル1[#Data]").EntireRow.Delete は、フィルタリングされた結果のデータ行全体を削除します。
最後の Range("テーブル1").AutoFilter 2 は、フィルタをクリアしています。
Range("A1").ListObject.ListColumns.Add
Range("A1").ListObject は、A1セルを含むテーブル全体を指します。.ListColumns.Add で、テーブルに新しい列を追加します。
Dim N As Long
N=Range("A1").ListObject.ListColumns.Count
Range("A1").Listobject.ListColumns(N).DataBodyRange.Select
テーブルに列を追加した後、その列のデータ範囲を選択します。
Dim N As Long は、整数型の変数 `N` を宣言します。N = Range("A1").ListObject.ListColumns.Count は、テーブル内の列数を `N` に代入します。
Range("A1").ListObject.ListColumns(N).DataBodyRange.Select は、追加された最後の列のデータ範囲を選択します。
Dim N As Long
Range("A1").ListObject.ListColumns.Add
N=Range("A1").ListObject.ListColumns.Count
Range("A1").Listobject.ListColumns(N).DataBodyRange="=[@点数]*2"
テーブルに列を追加し、その列に数式を設定しています。
Dim N As Long は、整数型の変数 `N` を宣言します。次に、Range("A1").ListObject.ListColumns.Add は、テーブルに新しい列を追加します。
N=Range("A1").ListObject.ListColumns.Count で追加された列数を `N` に代入します。
Range("A1").ListObject.ListColumns(N).DataBodyRange="=[@点数]*2" は、新しく追加された列(N 番目の列)に数式を設定しています。
"=[@点数]*2" は、列の各セルに対して数式を適用しています。
Range("テーブル1[[#Data],[#点数]").Offset(0,1)="=[@点数]*2"
特定の列に数式を挿入しています。Range("テーブル1[[#Data],[#点数]") は、「テーブル1」内の「点数」列のデータ範囲を表します。
.Offset(0,1)="=[@点数]*2" は、列の隣に新しい列を挿入し、数式 `=[@点数]*2` を列に適用します。
Excel VBAのスキルを活かして、開発者としてのキャリアを築いてみませんか? 当サイトでは、Excel VBAエキスパート認定資格取得に向けた情報を提供しています。興味がある方は、ぜひ以下の問題集からチャレンジしてみてください。 ExcelVBAエキスパートベーシック
仕事の効率をあげるためにExcelVBAを利用する人が対象の問題集です。
ExcelVBAエキスパートスタンダード
ExcelVBAの開発を仕事にする人が対象の問題集です。