VBAでExcelテーブルを使いこなす9つの方法【作成・編集・削除まで徹底解説】

テーブル(Table)は、ExcelでVBAを扱う際に最も重要かつ頻繁に使用されるオブジェクトのひとつです。この記事では、VBAを使ってExcelのテーブルを操作するさまざまな方法を、具体的なコード例とともに詳しく解説します。

VBAでExcelテーブルを操作する9つの方法

テーブルとは、先頭行に見出し(ヘッダー)が含まれる特殊なデータセットのことです。

VBAにおいて、テーブルはListObjectというオブジェクトとして扱われます。以下では、ExcelテーブルをVBAで活用できる9つの方法を順番に紹介していきます。

1. VBAでテーブルを作成する

まずは、VBAを使ってExcelにテーブルを作成する方法から学びましょう。

テーブルを作成するには、まず変数をListObject型として宣言します。その後、任意の範囲にテーブルを挿入します。

Dim Table1 as ListObject

Set Table1 = ActiveSheet.ListObjects.Add(xlSrcRange, Range("B4:D13"), , xlYes)

[ここでTable1はVBA上でのテーブル名、B4:D13はアクティブワークシート内でテーブルを作成したい範囲です。これらはお好みに合わせて変更できます。]

このコードを実行すると、アクティブシートの範囲B4:D13にテーブルが作成されます。

さらに、必要に応じてテーブル名を自由に変更することも可能です。

Table1.Name = "MyTable"

これでテーブル名がMyTableに変更されます。

2. 既存のテーブルを参照する

次に、すでに作成済みのテーブルをVBAで参照する方法を見ていきましょう。

既存のテーブルを参照する場合も、まず変数をListObject型として宣言します。その後、Excel上で設定されているテーブル名を指定して参照します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

これにより、Excel上のテーブル「MyTable」を「Table1」という名前で操作できるようになります。

3. テーブルに値を入力する

テーブル内に値を入力するには、まず対象のテーブルを参照します。その後、VBAのRange.Cellsプロパティを使用してセルに値を書き込みます。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.Range.Cells(2, 1) = "A Tale of Two Cities"

このコードを実行すると、アクティブシートのテーブルMyTableの2行目1列目に文字列「A Tale of Two Cities」が入力されます。

4. テーブルに行や列を挿入する

VBAでテーブルに行や列を挿入する場合も、まずテーブルを参照します。

行を追加するにはListRows.Addメソッドを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListRows.Add

これでテーブルMyTableの末尾に新しい行が追加されます。

同様に、列を追加するにはListColumns.Addメソッドを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListColumns.Add

これでテーブルMyTableの右側に新しい列が追加されます。

また、特定の位置に行や列を挿入したい場合は、ListRows.AddまたはListColumns.Addの引数に位置を指定します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListRows.Add (4)

これでテーブルMyTableの4行目として新しい行が挿入されます。

列の場合も同じ要領で位置を指定して追加できます。

5. テーブルからデータを読み取る

テーブルからデータを読み取る際も、VBAのRange.Cellsプロパティを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

MsgBox Table1.Range.Cells(3, 1)

コードを実行すると、テーブルMyTableの3行目1列目の値が表示されます。ここでは「The Forty Rules of Love」という値が取得されます。

6. テーブルの行や列をループ処理する

VBAでテーブルの各行を順番に処理するには、ListRows.Countプロパティを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")


For i = 1 To Table1.ListRows.Count

    Table1.ListRows(i).Range.Select

Next i

このコードは、テーブルTable1の各行を順番に選択しながら繰り返し処理を行います。

同様に、各列をループ処理する場合はListColumns.Countプロパティを使用します。

7. テーブル内の特定の値を検索する

テーブルの行や列から特定の値を検索するには、VBAのDataBodyRange.Rows.FindまたはDataBodyRange.Columns.Findメソッドを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Set Value = Table1.DataBodyRange.Columns(1).Find("Mother", LookAt:=xlWhole)

MsgBox Value

このコードは、テーブルMyTableの1列目から値「Mother」を検索し、見つかった場合はその値を返します。

8. テーブルのサイズを変更する

テーブルのサイズを新しい形状に変更するには、まず変更後の範囲をRangeとして設定します。その後、テーブルをその範囲に合わせてリサイズします。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

 
Dim Rng As Range

Set Rng = Range("B4:C8")

 
Table1.Resize Rng

このコードでは、まず範囲をB4:C8として設定し、その後テーブルMyTableをその新しい範囲にリサイズしています。

9. テーブルを削除する

VBAでテーブルを削除するには、Deleteメソッドを使用します。

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.Delete

これでアクティブワークシートからテーブルMyTableが削除されます。

覚えておきたいポイント

テーブルはVBAではListObjectとして扱われますが、通常のRangeオブジェクトとしてアクセスしたい場合は、Table.Rangeプロパティを使用しましょう。このプロパティはテーブルをRangeオブジェクトとして返すため、Rangeオブジェクトが持つすべてのプロパティやメソッドを利用できるようになります。

まとめ

本記事で紹介した9つの方法をマスターすれば、VBAを使ったテーブル操作を自由自在に行えるようになります。テーブルの作成から削除まで、日々の業務自動化にぜひ役立ててください。ご不明な点があれば、お気軽にお問い合わせください。

関連記事

• ExcelピボットテーブルをVBAで更新する方法(5つの実例)
• VBAですべてのピボットテーブルを更新する4つの方法
• Excelでピボットテーブルを編集する5つの方法
• ピボットテーブルの範囲を更新する5つの方法
• Excelテーブルで数式を効果的に使う方法(4つの例)