エクセル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つの行の範囲をそれぞれ取得して取り扱うことができます。

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

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

エクセル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の開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

コメント