【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法

★気に入ったらシェアをお願いします!


timetable-2467247_1920

みなさん、こんにちは。

なかじ(@nakaji098)です。

エクセルを実務で使っていると、表を作成したり、表に処理を行うことがあると思います。

表の範囲をRangeプロパティで直接指定することでも表に処理を行うことは可能です。
しかし、データは日々更新され、表の範囲も変わっていきます。そうなると、Rangeプロパティでは追加された範囲が含まれなかったり、逆に削除された行が範囲に含まれてしまったりします。

表の範囲に変化があったとしても、目的の表の範囲を取得したいですね。
そんな問題を解決してくれるのがCurrentRegionプロパティです。

今回は、CurrentRegionプロパティを使って、表の範囲が変わったとしても目的の表全体の範囲を取得する方法をご紹介したいと思います。

Rangeプロパティを使って表を指定する方法

まずは参考に、Rangeプロパティで表を指定する方法を説明から始めたいと思います。

Rangeプロパティの引数に範囲のアドレスを直接指定する

Rangeプロパティの引数に範囲のアドレスを直接指定することで、表全体の範囲を選択できます。

Worksheetオブジェクト.Range(アドレス)

セル選択
このようにRangeプロパティの引数に範囲のアドレスを直接指定することで、表の範囲を指定することもできます。

表に変更が加えられた場合

しかし、この方法で表の範囲を指定すると、表に変更が加えられたときに問題が起こります。

表にデータを追加して同じように実行してみます。(6行目とD列に追加)

セル選択2
このように追加された範囲(6行目とD列)が含まれなくなってしまいます。

これでは正確に表の範囲を指定することができませんね。

実際に実務でエクセルを使用していると、表の範囲が決まっていることは少なく、データが増えたりしますね。そうなるとRangeプロパティの引数に範囲のアドレスを直接指定することで表の範囲を直接指定する方法では対応できません。

CurrentRegionプロパティを使って表の範囲を取得する方法

そんな時に使えるのが、CurrentRegionプロパティになります。

Rangeオブジェクト.CurrentRegion

このプロパティを使用することで、指定したRangeオブジェクトのアクティブセル(=空白行と空白列で囲まれたセル範囲)を取得することができます。

少しわかりづらいと思いますので例を挙げます。
アクティブセル
この表ですと、水色に着色された空白行と空白列に囲まれた、A1セル~C3セルがアクティブセルになります。

CurrentRegionプロパティを使って表の範囲を取得

それでは、実際にCurrentプロパティを使ってみましょう。

表範囲選択

A1セル~C5セルが選択されました。
とても簡単で便利な機能です!

このプロパティで取得できる表の範囲は、ExcelでCtrlShiftキーを押すのと同じ範囲です。なので、プログラムを作っていて、表の範囲がCurrentRegionプロパティで取得できるかを確認したいときは、表の範囲内のセルを指定してCtrlShiftキーを押すことで確認することもできます

表に変更が加えられた場合

表にデータを追加して実行してみましょう。(6行目とD列に追加)

広範囲の表選択
表を広げた範囲(A1セル~D6セル)まで自動的に選択されました!

ここではA1セルを指定しましたが、ここで指定するセルは、表の範囲に含まれているセルであればどのセルでも大丈夫です。

このように表の範囲に含まれるセルであれば、正しく表の範囲を取得することができます。

1行目と1列目が空白の場合

表の中には、1行目と1列目が空白で作られている表もあると思います。
そのときにCurrentRegionプロパティを使うと、どのように範囲を取得するかを見てみましょう。

広範囲の表選択

A1セルをオブジェクトに指定し実行すると、このようにA1セル~D6セルが選択されてしまいます。

なので、ここで指定するRangeオブジェクトは表の範囲に含まれているセル(B2セル~D6セル)にする必要があります。

空白あり表選択2
このようにすることで、正しく表の範囲が選択できます。

空白行がある場合

それでは、表の中に空白行がある場合も見ていきましょう。

先ほども説明しましたが、CurrentRegionプロパティで取得できる範囲は、Rangeオブジェクトのアクティブセル(=空白行と空白列で囲まれたセル範囲)です。

ということは、空白行が入っていると、取得できる表の範囲が空白行の前までとなってしまいます。

空白行
このように選択されたのはA1セル~D4セルです。
CurrentRegionプロパティで表を取得するときは、表に空白の行がないかに気をつける必要があります。

まとめ

エクセルVBAで表の範囲を自動的に取得する方法を紹介しました。

まずはじめに、Rangeプロパティの引数に範囲のアドレスを直接指定し表全体の範囲を選択する方法から説明しました。

しかし、表は変更されることもあります。そこでCurrentRegionプロパティを使うことで、表の範囲が変更されても表全体の範囲を自動的に取得することができます。

また、空白行や空白列が入っていた場合の注意点なども説明しました。

表全体の範囲を取得するときは、このCurrentRegionプロパティを使ってみてください!

連載目次:エクセルVBAでシートのセル範囲を簡単に取得する方法

エクセルVBAでシート上のセル範囲を取得。いつも必要になるのですが、これが意外と難しいと思われませんか? 本シリーズではシート上のデータがあるセル範囲を簡単に、そして上手に取得する方法をお伝えしていきます。
  1. 【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
  2. 【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法
  3. 【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法
  4. 【エクセルVBA】Endプロパティで表全体の範囲を取得する方法

The following two tabs change content below.

なかじ

1985年生まれ。東京在住。 業務を効率化するために始めたエクセルVBAにすっかり虜に。いまではVBAなしの業務など考えられません。そんなVBAの魅力をわかりやすく伝えていきたいです。 趣味は読書。好きな本のジャンルはビジネス系と心理学。年間100冊は読む、本の虫。 特技は独学。TOEIC900over、ファイナンシャルプランナーなど、独学で取得してます。

コメント

  1. たつろう より:

    『ExcelVBAを実務で使い倒す技術』拝読させていただきました。
    A1セルから表が始まっているのであればUsedRangeも使えると思います。
    空白行には強くなりますが、表外のセルにメモなど書いてしまうとそこまで取得されてしまう弱点がありますが。
    まさに、人やさしい形式とPCにやさしい形式の問題ですね。

    • たつろう様

      コメントおよび拙著のご購読ありがとうございます!

      おっしゃる通り、UsedRangeも使えますね!
      後日、記事として提供させて頂ければと思います。

      今後ともどうぞよろしくお願いいたします!