テーブル操作のVBAコード（ListObject,DataBodyRange）

公開日：2019-05-12　最終更新日：2022-08-01

第131回.テーブル操作のVBAコード（ListObject,DataBodyRange）

テーブル操作の具体的なVBAコードをパターン別に掲載します、

テーブルの全体的な構成は

・テーブル全体
・見出し行
・データ範囲
・集計列
・集計行

VBAでは、これらのオブジェクトを使い各要素にアクセスします。
テーブル全体のオブジェクトがListObjectで、
シート内に複数テーブルが作成可能なので、
シート内のListObjectのコレクションがListObjectsになります。

ListObjectsおよびListObjectのメンバー一覧（メソッド、プロパティ）については前回の、
第130回.テーブル操作の概要（ListObject）

・ListObjects コレクション・ListObject オブジェクト・テーブル操作のVBAコード

こちらを参照してください。

全体目次

テーブル操作のVBAサンプル使用例
テーブルに設定
テーブルスタイル一覧
テーブルの存在確認
テーブルを範囲に変換
テーブルの範囲を再設定
テーブルのセルに値を入れる
テーブルのセルの数式変更
テーブルの行・列のクリア
テーブルの列の数式設定
テーブルの行挿入・削除
テーブルの列挿入・削除
テーブルのオートフィルター
テーブルの並べ替え（ソート）
テーブルの集計行挿入・削除・非表示
テーブルの右端に集計列追加
テーブルに新しい行列を含めない
テーブルの使い方の基本
テーブル全件処理とデータ最終行（DataBodyRange） ・・・後日追加の別ページ

・テーブル全件処理・テーブルの特定列のデータ最終行まで・テーブルが拡張されないパターン・サイト内のテーブルに関する他のページ

テーブル操作のVBAサンプル使用例

細かい説明は必要ないでしょう。

テーブルオブジェクト(ListObject)、データ範囲(DataBodyRange)、行(ListRows)、列(ListColumns)

これらを意識して順にたどるように記述していくことで目的のオブジェクトにたどり着けます。
実際に動かして（時にステップイン(F8)で）確認してみましょう。

VBAサンプルとして意図的にいろいろな書き方をしていますので、VBAコードの書き方の参考にしてください。

以下では、基本的にはB2セル起点のセルの範囲を想定してVBAコードを書いていますが、
セル位置の指定は、適宜読み替えてください。

テーブルに設定

ListObjects.Add(SourceType,Source,linksource,xllistthasheaders,Destination,TableStyleName)


  
    
      Sub テーブルに設定()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

      　　Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range("B2").CurrentRegion,
      , xlYes)

　　tbl.Name = "テーブル1"

      　　tbl.TableStyle = "TableStyleLight2"

End Sub

TableStyleはAddと同時に指定することもできます。
Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range("B2").CurrentRegion, , xlYes, , "TableStyleLight2")


  
    
      Sub テーブルに設定_汎用()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = fncテーブル化(ws.Range("B2").CurrentRegion)

　　If Not tbl Is Nothing Then

　　　　tbl.Name = "テーブル1"

　　　　tbl.TableStyle = "TableStyleLight2"

　　End If

      End Sub


Function fncテーブル化(ByVal argRange As Range) As ListObject

　　Set fncテーブル化 = Nothing

　　Dim ws As Worksheet

　　Set ws = argRange.Worksheet

      　　Dim tbl As ListObject


      　　'指定セル範囲が既存テーブルと重なっているか

　　For Each tbl In ws.ListObjects

　　　　If Not Intersect(argRange, tbl.Range) Is Nothing Then

　　　　　　Exit Function

　　　　End If

      　　Next


      　　'オートフィルターの範囲と重なっているか

　　If ws.AutoFilterMode Then

　　　　If Not Intersect(argRange, ws.AutoFilter.Range) Is Nothing Then

　　　　　　Exit Function

　　　　End If

      　　End If


      　　'指定セル範囲をテーブル化

　　On Error Resume Next

　　Set fncテーブル化 = ws.ListObjects.Add(xlSrcRange, argRange, , xlYes)

　　If Err Then

　　　　MsgBox Err.Description

　　End If

End Function

テーブルスタイル一覧


  
    
      Sub テーブルスタイル一覧()

　　Dim tblStyle As TableStyle

　　For Each tblStyle In ThisWorkbook.TableStyles

　　　　Debug.Print tblStyle.Name

　　Next

      End Sub

テーブルの存在確認


  
    
      Sub テーブルの存在確認1()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　If ws.ListObjects.Count > 0 Then

　　　　MsgBox "テーブル有"

　　End If

End Sub


  
    
      Sub テーブルの存在確認2()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　If Not ws.Range("B2").ListObject Is Nothing Then

　　　　MsgBox "テーブル有"

　　End If

End Sub

テーブルを範囲に変換


  
    
      Sub 範囲に変換()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　'Set tbl = ws.ListObjects(1)

　　'Set tbl = ws.Range("B2").ListObject

　　tbl.TableStyle = ""

　　tbl.Unlist

End Sub


  
    
      Sub 範囲に変換_シート全て()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　For Each tbl In ws.ListObjects

　　　　tbl.TableStyle = ""

　　　　tbl.Unlist

　　Next

End Sub

テーブルの範囲を再設定


  
    
      Sub テーブルの範囲を再設定()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.Range("B2").ListObject

　　tbl.Resize ws.Range("B2").CurrentRegion

End Sub

テーブルのセルに値を入れる


  
    
      Sub セルに値を入れる()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　tbl.ListRows(4).Range(2) = "あ"

　　tbl.ListColumns("列2").Range(6) = "い"

      　　tbl.ListRows(6).Range(tbl.ListColumns("列2").Index) = "う"

End Sub

テーブルのセルの数式変更


  
    
      Sub セルの数式変更()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

      　　tbl.ListRows(4).Range(3) = "=[@列1]+[@列2]"

　　tbl.ListColumns("列3").Range(6) = "=[@列1]+[@列2]"

　　tbl.ListRows(3).Range(tbl.ListColumns("列3").Index) = "=[@列1]+[@列2]"

End Sub

@は同じ行を意味していますので、[@列1]は、「列1」の同じ行の値になります。

テーブルの行・列のクリア


  
    
      Sub 列のクリア()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　col = tbl.ListColumns("列3").Index

　　tbl.DataBodyRange.Columns(col).ClearContents

End Sub


  
    
      Sub 行のクリア()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　tbl.ListRows(5).Range.ClearContents

End Sub

テーブルの列の数式設定


  
    
      Sub 列の数式設定()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　col = tbl.ListColumns("列3").Index

　　tbl.DataBodyRange.Columns(col) = "=[@列1]+[@列2]"

End Sub


  
    
      Sub 列の数式設定_個別1()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　col = tbl.ListColumns("列3").Index

　　Dim myRange As Range

　　For Each myRange In tbl.DataBodyRange.Columns(col)

　　　　myRange = "=[@列1]+[@列2]"

　　Next

End Sub


  
    
      Sub 列の数式設定_個別2()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　col = tbl.ListColumns("列3").Index

　　Dim myRow As ListRow

　　For Each myRow In tbl.ListRows

　　　　myRow.Range(col) = "=[@列1]+[@列2]"

　　Next

End Sub

@は同じ行を意味していますので、[@列1]は、「列1」の同じ行の値になります。

テーブルの行挿入・削除


  
    
      Sub 行挿入()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

      　　tbl.ListRows.Add Position:=5

End Sub

ListRowsオブジェクト.Add (Position, always insert)

最終行の下に追加する場合は、Positionを省略してください。

AlwaysInsert

テーブルの最終行より下のセルのデータを移動するかどうかを指定します。
Trueの場合、表の下のセルは1行下に移動します。
Falseの場合、テーブルの下の行が空の場合は、テーブルが拡張されて、その行を使用するようになります。ただし、テーブルの下の行にデータが含まれている場合、それらのセルは新しい行が挿入されるときにシフトされます。
省略時はTrueになります。


  
    
      Sub 行削除()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　tbl.ListRows(5).Delete

End Sub

テーブルの列挿入・削除


  
    
      Sub 列挿入()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

      　　tbl.ListColumns.Add Position:=3

　　tbl.HeaderRowRange(3) = "追加列"

End Sub

Position:=の直前列に追加されます。
最後列に追加する場合は、Position:=を省略してください。


  
    
      Sub 列削除()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　tbl.ListColumns(3).Delete

End Sub

テーブルのオートフィルター


  
    
      Sub オートフィルター()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　With tbl

　　　　With .DataBodyRange

　　　　　　.AutoFilter Field:=tbl.ListColumns("列1").Index, Criteria1:=4

　　　　　　If tbl.Range.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then

　　　　　　　　.EntireRow.Delete

　　　　　　　　'Application.DisplayAlerts = False

　　　　　　　　'.Delete

　　　　　　End If

　　　　End With

　　　　.AutoFilter.ShowAllData

　　End With

End Sub

テーブルの並べ替え（ソート）


  
    
      Sub ソート()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　With tbl

　　　　With .Sort

　　　　　　.SortFields.Clear

　　　　　　.SortFields.Add Key:=tbl.HeaderRowRange(1), _

　　　　　　　　　　 SortOn:=xlSortOnValues, _

　　　　　　　　　　 Order:=xlDescending

　　　　　　.Header = xlYes

　　　　　　.Apply

　　　　End With

　　End With

End Sub

テーブルの集計行挿入・削除・非表示


  
    
      Sub 集計行挿入()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　tbl.ShowTotals = True

　　Dim col As ListColumn

　　For Each col In tbl.ListColumns

　　　　col.TotalsCalculation = xlTotalsCalculationSum

　　Next

End Sub


  
    
      Sub 集計行削除()

      　　Dim ws As Worksheet

      　　Set ws = ActiveSheet

      　　Dim tbl As ListObject

      　　Set tbl = ws.ListObjects("テーブル1")

      　　tbl.TotalsRowRange.Delete

      End Sub


  
    
      Sub 集計行非表示()

      　　ActiveSheet.ListObjects("テーブル1").ShowTotals = False

End Sub

テーブルの右端に集計列追加


  
    
      Sub 右端に集計列追加()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　With tbl.HeaderRowRange

　　　　.Resize(1, 1).Offset(, .Count) = "集計列"

　　　　.Resize(1, 1).Offset(1, .Count) = "=SUM([@[" & .Item(2) & "]:[" & .Item(6) & "]])"

　　End With

End Sub

テーブルに新しい行列を含めない

このような使い方は良くないのですが、解説として一応載せておきます。

テーブルに隣接するセルに入力すると自動的にテーブル範囲が拡張されます。
これはオプションで止めることができます。

VBAで一時的にこれを停止して、セルに値をいれてから元に戻しておくVBAになります。


  
    
      Sub 新しい行列を含めない()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

　　Set tbl = ws.ListObjects("テーブル1")

　　Application.AutoCorrect.AutoExpandListRange = False

　　Range("H6") = 1

　　Application.AutoCorrect.AutoExpandListRange = True

End Sub

テーブルの使い方の基本

最後の「新しい行列を含めない」のような使い方は極めて良くない使い方になります。
テーブルを使う時の基本、といいますか、シートを使う時の基本になります。

・1シート1テーブル
・1行めに見出し、2行目からデータ（テーブルなら当然ですが）
・表範囲以外に余分なデータは入れない

ここを守って使えば、テーブルはとても強力かつ便利な機能です。

サイト内のテーブルに関するページ

第130回.テーブル操作の概要（ListObject）

・ListObjects コレクション・ListObject オブジェクト・テーブル操作のVBAコード

第142回.テーブル全件処理とデータ最終行

・テーブル全件処理・テーブルの特定列のデータ最終行まで・テーブルが拡張されないパターン・サイト内のテーブルに関する他のページ

44本目：全テーブル一覧作成｜VBA100本ノック

・出題・頂いた回答・解説・補足・サイト内関連ページ

45本目：テーブルに列追加｜VBA100本ノック

・出題・頂いた回答・解説・補足・サイト内関連ページ

53本目：テーブルの扱いと年齢計算｜VBA100本ノック

・出題・頂いた回答・解説・補足・サイト内関連ページ

同じテーマ「マクロVBA入門」の記事

第124回.Workbookのイベントプロシージャー
 第125回.Worksheetのイベントプロシージャー
 第130回.テーブル操作の概要（ListObject）
第131回.テーブル操作のVBAコード（ListObject,DataBodyRange）
第142回.テーブル全件処理とデータ最終行（ListObject,DataBodyRange）
第127回.他のブックのマクロを実行（Runメソッド）
第128回.マクロをショートカットで起動（OnKeyメソッド）
第129回.レジストリの操作（SaveSetting,GetSetting,GetAllSettings,DeleteSetting）
第133回.引数の数を可変にできるパラメーター配列（ParamArray）
第134回.Errオブジェクトとユーザー定義エラー
 第138回.外部ライブラリ（ActiveXオブジェクト）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門

ホーム
マクロVBA入門編
マクロVBA入門
テーブル操作のVBAコード（ListObject,DataBodyRange）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA入門
テーブル操作のVBAコード（ListObject,DataBodyRange）

第131回.テーブル操作のVBAコード（ListObject,DataBodyRange）

全体目次

テーブル操作のVBAサンプル使用例

テーブルに設定

テーブルスタイル一覧

テーブルの存在確認

テーブルを範囲に変換

テーブルの範囲を再設定

テーブルのセルに値を入れる

テーブルのセルの数式変更

テーブルの行・列のクリア

テーブルの列の数式設定

テーブルの行挿入・削除

テーブルの列挿入・削除

テーブルのオートフィルター

テーブルの並べ替え（ソート）

テーブルの集計行挿入・削除・非表示

テーブルの右端に集計列追加

テーブルに新しい行列を含めない

テーブルの使い方の基本

サイト内のテーブルに関するページ

同じテーマ「マクロVBA入門」の記事

新着記事NEW ・・・新着記事一覧を見る

アクセスランキング・・・ランキング一覧を見る

第131回.テーブル操作のVBAコード（ListObject,DataBodyRange）

全体目次

テーブル操作のVBAサンプル使用例

テーブルに設定

テーブルスタイル一覧

テーブルの存在確認

テーブルを範囲に変換

テーブルの範囲を再設定

テーブルのセルに値を入れる

テーブルのセルの数式変更

テーブルの行・列のクリア

テーブルの列の数式設定

テーブルの行挿入・削除

テーブルの列挿入・削除

テーブルのオートフィルター

テーブルの並べ替え（ソート）

テーブルの集計行挿入・削除・非表示

テーブルの右端に集計列追加

テーブルに新しい行列を含めない

テーブルの使い方の基本

サイト内のテーブルに関するページ

同じテーマ「マクロVBA入門」の記事

新着記事NEW ・・・新着記事一覧を見る

アクセスランキング ・・・ ランキング一覧を見る

アクセスランキング・・・ランキング一覧を見る