エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法


tables

photo credit: dejankrsmanovic Empty Bar Chairs via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAで請求書作成マクロを題材としてテーブルを操作する方法をお伝えしています。

前回の記事はコチラです。

エクセルVBAでテーブル名を使用してテーブルを取得する方法
請求書マクロの作成を題材として、エクセルVBAでテーブルを操作する方法についてお伝えしています。今回は、テーブル名の変更する方法と、テーブル名を使用してテーブルを取得する方法について解説をしていきます。

テーブルを名前で取得する方法についてお伝えしました。

今回は、元となる請求データファイルのテーブルを使って、VBAから見たテーブルの構造について確認していきます。

では、エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法です。

行ってみましょう!

テーブルを構成する3つの「行」

前回 紹介した、「請求データ.xlsm」を題材として、テーブルがVBAでどのような構造になっているか、確認してみましょう。

テーブルの行の構成に注目です。

「データ」シートの「請求データ」テーブルを選択した状態で、リボンの「テーブルツール」→「デザイン」の「見出し行」と「集計行」のチェックを入れたり、外したりしてみましょう。

エクセルでテーブルの見出し行・集計行を設定する

すると、テーブルの行構成が以下の3つからできているということがわかるでしょう。

すなわち、以下の3種類です。

  • 見出し行
  • 行(いわゆるデータの行)
  • 集計行

エクセルのテーブルを構成する3つの行

エクセルVBAでテーブルの行範囲を取得する

VBAから見た場合、この3つの行の範囲をそれぞれ取得して取り扱うことができます。

例えば、以下のようなプロシージャを実行してみましょう。

Sub テーブルの行範囲の取得()

With wsData.ListObjects("請求データ")
    Debug.Print .HeaderRowRange.Address
    Debug.Print .DataBodyRange.Address
    Debug.Print .TotalsRowRange.Address
End With

End Sub

実行すると、イミディエイトウィンドウで以下のような結果が得られます。

エクセルVBAでテーブルの各行範囲のアドレスを取得

それぞれの行範囲を取得して、そのアドレスを出力しているという処理になりますね。

以下解説をしていきましょう。

テーブルの見出し行の範囲を取得する

テーブルの見出し行の範囲を取得するにはHeaderRowRangeプロパティを使います。

ListObjectオブジェクト.HeaderRowRange

見出し行は1行になりますので、1行分のセル範囲が得られます。

テーブルのデータ行の範囲を取得する

テーブルのデータ行の範囲を取得するにはDataBodyRangeプロパティを使います。

ListObjectオブジェクト.DataBodyRange

データ行は複数行あることが一般的ですね。

このプロパティでセル範囲を取得することができるのですが、実際にループ処理などをする場合には、ListRowsプロパティを使うほうが便利かも知れません。

ListRowsプロパティについては、また別途解説をします。

テーブルの集計行の範囲を取得する

テーブルの集計行の範囲を取得するにTotalsRowRangeプロパティを使います。

TotalRowRangeではなくて、TotalsRowRangeなので注意です。

ListObjectオブジェクト.TotalsRowRange

こちらも1行分のセル範囲になります。

テーブルに行追加をした場合のセル範囲

では、テーブルのメリットももうひとつ示していきましょう。

例えば、以下のように適当に1行 追加して、前述のプロシージャを実行してみましょう。

エクセルのテーブルに1行追加

すると、以下のような出力になります。

エクセルでテーブルに行追加した場合の行範囲をデバッグ出力

DataBodyRangeプロパティで取得できるデータ行のセル範囲は1行分、広がっていますし、TotalsRowRangeプロパティで取得できる集計行のセル範囲は1行分プラス方向にずれていますね。

このようにテーブル化をしておくと、それを構成する各要素の行範囲は、エクセルのほうでしっかりと管理してくれているんですね。

つまり、テーブルは行追加や削除などの変更に強いということなんです。

逆に、テーブルを使わない場合に、ユーザーの操作により「データ行が1行増えた」「集計行が1行ずれた」というのを感知するのは、かなり大変なのです。

このありがたみ、わかりますでしょうか?

まとめ

以上、エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法についてお伝えしました。

本文でお伝えしたとおり、テーブルにしておくと各行範囲はエクセルのほうで勝手に「ここからここまでだよね」というのを把握しておいてくれます。

テーブルはデータの追加や削除などの変更に強いのです。

さて、次回ですがListRowsコレクションを使って、データ行を走査する方法についてお伝えします。

エクセルVBAでテーブルのデータ行についてループ処理を行う方法
請求書マクロを題材にエクセルVBAでテーブル操作する方法をお伝えしています。今回は、ListRowsオブジェクトがポイント。テーブルのデータ行についてループ処理を行う方法についてお伝えしていきます。

どうぞお楽しみに!

連載目次:エクセルVBAでテーブルを活用した請求書マクロを作る

エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。
  1. エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得
  2. エクセルVBAでテーブル名を使用してテーブルを取得する方法
  3. エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法
  4. エクセルVBAでテーブルのデータ行についてループ処理を行う方法
  5. エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
  6. エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ
  7. エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方
  8. エクセルVBAで請求書作成マクロをテーブルを活用して作る
  9. エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

タイトルとURLをコピーしました