VBA入門
第142回.テーブル全件処理とデータ最終行(ListObject,DataBodyRange)

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
最終更新日:2020-09-01

第142回.テーブル全件処理とデータ最終行(ListObject,DataBodyRange)


VBA テーブル ListObject

テーブルの全件処理と実際にデータが入っている最終行の取得について具体的なVBAを掲載します。
注意点として、最新の365でテーブルが拡張されないパターンについても説明しておきます。


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

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

他のテーブル操作のVBAコードのサンプル ・・・ 以下は別ページです

テーブル全件処理

Sub テーブル全件処理()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  
  Dim c1 As Long, c2 As Long, c3 As Long
  c1 = tbl.ListColumns("列1").Index
  c2 = tbl.ListColumns("列2").Index
  c3 = tbl.ListColumns("列3").Index
  
  Dim i As Long
  With tbl.DataBodyRange
    For i = 1 To .Rows.Count
      .Cells(i, c3).Value = .Cells(i, c1).Value / .Cells(i, c2).Value
    Next
  End With
End Sub

テーブル内で、
3列目 = 1列目 / 2列目
これをテーブル範囲の全行に対して行っています。

DataBodyRange
DataBodyRangeは、
テーブルの見出しの行を除く、値の範囲を表すRangeオブジェクトを取得します。
値の取得のみ可能です。

テーブルの特定列のデータ最終行まで

VBA マクロ テーブル

テーブルがこのような状態の時に、
3列目 = 1列目 / 2列目
この処理を行おうとして、先の「テーブル全件処理」を実行すればエラーになることはお分かりいただけると思います。
分母のセルが空欄では割り算が出来ません。
そこで、データの入っている最終行を取得して、そこまでの処理にします。
もちろん、割り算の前にIfで分母を確認するようにVBAを書かなければならないものです。
あくまで、以下のVBAの実行テストとして分かり易い例にしたものです。

Function テーブルの特定列のデータ最終行まで()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  
  Dim c1 As Long, c2 As Long, c3 As Long
  c1 = tbl.ListColumns("列1").Index
  c2 = tbl.ListColumns("列2").Index
  c3 = tbl.ListColumns("列3").Index
  
  Dim lastRow As Long
  lastRow = TableLastRow(tbl, tbl.ListColumns("列1").Index)

  Dim i As Long
  With tbl.DataBodyRange
    For i = 1 To lastRow
      .Cells(i, c3).Value = .Cells(i, c1).Value + .Cells(i, c2).Value
    Next
  End With
End Function

Function TableLastRow(ByVal tbl As ListObject, ByVal argCol As Variant) As Long
  '列名と列Indexの両対応
  Dim col As Long
  If IsNumeric(argCol) Then
    col = argCol
  Else
    col = tbl.ListColumns(argCol).Index
  End If
  
  'テーブルの下から順にデータの入っている行を探す
  Dim i As Long
  With tbl.DataBodyRange
    For i = .Rows.Count To 1 Step -1
      If .Cells(i, col).Value <> "" Then
        TableLastRow = i
        Exit Function
      End If
    Next
  End With
End Function

TableLastRowでは、指定列の一番下から上に向かって空欄以外のセルを探しています。

テーブルが拡張されないパターン

テーブル範囲の右隣の列や直ぐ下の行にデータを入力すると、テーブル範囲は自動的に拡張されます。
しかし、これはエクセルの機能に委ねているものなので絶対に大丈夫とは言い切れないようです。

最新の365では、
右隣の列や直ぐ下の行にいきなりハイパーリンクを設定した場合、テーブル範囲が拡張されなくなっています。
その後に、その列やその行にデータを普通に入力してもテーブル範囲は拡張されなくなってしまいます。
これは、手動でハイパーリンクを設定した場合、VBAで設定した場合、どちらでも同様に発生します。
※2020年9月1日現在、
筆者の365および2019において確認されたものです。
2013および2010では、この現象は発生していません。

Sub テーブルが拡張されないパターン()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim tbl As ListObject
  Set tbl = ws.ListObjects("テーブル1")
  
  Dim c1 As Long
  c1 = tbl.ListColumns("列1").Index
  
  Dim lastRow As Long
  lastRow = tbl.DataBodyRange.Rows.Count
  lastRow = lastRow + 1 'テーブル範囲のすぐ下
  
  With tbl.DataBodyRange
    tbl.Parent.Hyperlinks.Add Anchor:=.Cells(lastRow, 1), _
                 Address:="リンク先", _
                 TextToDisplay:="ハイパーリンク"
    .Cells(lastRow, 2).Value = "列2のデータ"
  End With
End Sub

このVBAを実行しても、テーブル範囲は拡張されません。

以下のように、先に普通のデータを入れることで自動拡張されるようになります。

  With tbl.DataBodyRange
    .Cells(lastRow, 2).Value = "列2のデータ"
    tbl.Parent.Hyperlinks.Add Anchor:=.Cells(lastRow, 1), _
                 Address:="リンク先", _
                 TextToDisplay:="ハイパーリンク"
  End With

  With tbl.DataBodyRange
    .Cells(lastRow, 1) = "ハイパーリンク"
    tbl.Parent.Hyperlinks.Add Anchor:=.Cells(lastRow, 1), _
                 Address:="リンク先", _
                 TextToDisplay:=.Cells(lastRow, 1).Value
    .Cells(lastRow, 2).Value = "列2のデータ"
  End With



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

第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オブジェクト)
第140回.Property {Get|Let|Set} ステートメント


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

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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