第142回.テーブル全件処理とデータ最終行（ListObject,DataBodyRange）
テーブルの全件処理と実際にデータが入っている最終行の取得について具体的なVBAを掲載します。
注意点として、最新の365でテーブルが拡張されないパターンについても説明しておきます。
・見出し行
・データ範囲
・集計列
・集計行
テーブル全体のオブジェクトがListObjectで、
シート内に複数テーブルが作成可能なので、
シート内のListObjectのコレクションがListObjectsになります。
第130回.テーブル操作の概要（ListObject）
他のテーブル操作の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列目
これをテーブル範囲の全行に対して行っています。
テーブルの見出しの行を除く、値の範囲を表すRangeオブジェクトを取得します。
値の取得のみ可能です。
テーブルの特定列のデータ最終行まで
分母のセルが空欄では割り算が出来ません。
そこで、データの入っている最終行を取得して、そこまでの処理にします。
あくまで、以下の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では、指定列の一番下から上に向かって空欄以外のセルを探しています。
テーブルが拡張されないパターン
しかし、これはエクセルの機能に委ねているものなので絶対に大丈夫とは言い切れないようです。
右隣の列や直ぐ下の行にいきなりハイパーリンクを設定した場合、テーブル範囲が拡張されなくなっています。
その後に、その列やその行にデータを普通に入力してもテーブル範囲は拡張されなくなってしまいます。
これは、手動でハイパーリンクを設定した場合、VBAで設定した場合、どちらでも同様に発生します。
筆者の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
サイト内のテーブルに関する他のページ
44本目：全テーブル一覧作成｜VBA100本ノック
同じテーマ「マクロ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 ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。