ExcelマクロVBA入門
第131回.テーブル操作のVBAコード(ListObject)

Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
最終更新日:2019-08-02

第131回.テーブル操作のVBAコード(ListObject)


VBA テーブル ListObject

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


テーブルの全体的な構成は
・テーブル全体
・見出し行
・データ範囲
・集計列
・集計行
VBAでは、これらのオブジェクトを使い各要素にアクセスします。
テーブル全体のオブジェクトがListObjectで、
シート内に複数テーブルが作成可能なので、
シート内のListObjectのコレクションがListObjectsになります。

ListObjectsおよびListObjectのメンバー一覧(メソッド、プロパティ)については前回の、
第130回.テーブル操作の概要(ListObject)
テーブルのVBAでの操作で使用するオブジェクトの概要の説明です、テーブルは、セルの範囲を表(テーブル)に変換することで、関連するデータの管理と分析を容易にできるエクセルの機能で、以前はリストと呼ばれていました。テーブルを作成して書式設定することで、データを視覚的にグループ化して分析しやすくできます。
こちらを参照してください。

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

細かい説明は必要ないでしょう。
テーブル全体(ListObject)、行(ListRow)、列(ListColumn)
これらを意識して順にたどるように記述していくことで目的のオブジェクトにたどり着けます。
実際に動かして(時にステップイン(F8)で)確認してみましょう。

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

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

VBA テーブル ListObject

テーブルに設定:テーブル操作のVBAサンプル

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



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

テーブルスタイル一覧:テーブル操作のVBAサンプル

Sub テーブルスタイル一覧()
  Dim tblStyle As TableStyle
  For Each tblStyle In ThisWorkbook.TableStyles
    Debug.Print tblStyle.Name
  Next
End Sub 

テーブルの存在確認:テーブル操作のVBAサンプル

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

範囲に変換:テーブル操作のVBAサンプル

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

テーブルの範囲を再設定:テーブル操作のVBAサンプル

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

セルに値を入れる:テーブル操作のVBAサンプル

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

行・列のクリア:テーブル操作のVBAサンプル

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

列の数式設定:テーブル操作のVBAサンプル



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

行挿入・削除:テーブル操作のVBAサンプル

Sub 行挿入()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  tbl.ListRows.Add 5
End Sub

Sub 行削除()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  tbl.ListRows(5).Delete
End Sub

列挿入・削除:テーブル操作のVBAサンプル

Sub 列挿入()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  tbl.ListColumns.Add 3
  tbl.HeaderRowRange(3) = "追加列"
End Sub

Sub 列削除()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  tbl.ListColumns(3).Delete
End Sub

オートフィルター:テーブル操作のVBAサンプル

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

並べ替え(ソート):テーブル操作のVBAサンプル

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

集計行挿入・削除・非表示:テーブル操作のVBAサンプル

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 = True
End Sub

右端に集計列追加:テーブル操作のVBAサンプル

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 テーブル ListObject

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行目からデータ
・表範囲以外に余分なデータは入れない


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



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

第122回.Shell関数
第123回.APIについて(Win32API)
第124回.Workbookのイベントプロシージャー
第125回.Worksheetのイベントプロシージャー
第126回.入力規則(Validation)
第127回.他のブックのマクロを実行(Runメソッド)
第128回.マクロをショートカットで起動(OnKeyメソッド)
第129回.レジストリの操作(SaveSetting,GetSetting,GetAllSettings,DeleteSetting)
第130回.テーブル操作の概要(ListObject)
第131回.テーブル操作のVBAコード(ListObject)
第132回.その他のExcel機能(グループ化、重複の削除、オートフィル等)


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

文字列結合&でコンパイルエラーになる理由|VBA技術解説(9月19日)
Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



  • >
  • >
  • >
  • テーブル操作のVBAコード(ListObject)

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


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




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