【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法

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


UsedRange画像

みなさん、こんにちは。
なかじ(@nakaji098)です。

前回は、CurrentRegionプロパティを使って、目的の表の範囲を取得する方法をご紹介しました。

【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
表の範囲をRangeプロパティで直接指定することでも表に処理を行うことは可能です。しかし、データは日々更新され、表の範囲も変わっていきます。そんな問題を解決してくれるのがCurrentRegionプロパティです。

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

ただ、CurrentRegionプロパティを使用すると、表に空白行がある場合は、取得できる表の範囲が空白行の前までとなってしまいます。

そこで今回は、UsedRangeプロパティを使ってシートで使用されているセルの範囲全てを取得する方法についてご紹介していきたいと思います。
このUsedRangeプロパティを使うことで、表に空白行がある場合でも表全体の範囲を取得することができます。

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

まず、前回のおさらいとしてCurrentRegionプロパティを使って表の範囲を取得する方法を説明します。

Rangeオブジェクト.CurrentRegion

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

表範囲選択
A1セル~C5セルが選択されました。

このようにCurrentRegionプロパティを使うことで簡単に表全体の範囲を取得することができます。

表に空白行がある場合

しかし、CurrentRegionプロパティを使って表を取得する場合に気をつけなければならない点があります。

それが表に空白行がある場合です。

空白行
選択されたのはA1セル~D4セルで、E行以降は選択されません。
このように表に空白行があると、選択されるのは空白行の前までになります。

CurrentRegionプロパティで表を取得するときは、表に空白行がないかに気をつける必要があります。

UsedRangeプロパティを使ってシートで使用されているセルの範囲全てを取得する方法

表の範囲を取得する方法として、別の方法もあります。
そのひとつがUsedRangeプロパティになります。

Worksheetオブジェクト.UsedRange

UsedRangeプロパティは、指定されたワークシートで使用されているセル範囲を返します。

具体例を挙げてみます。
まず、C4セルにのみデータが入力されて状態で実行してみます。
C4セル

C4セル選択
C4セルが選択されました。

次は、A1セルにもデータを入力して実行します。
A1C4セル

A1C4セル選択

A1セル~C4セルが選択されました。

最後の例として、B2セルとD5セルにデータが入力された状態で実行します。
B2D5セル

B2D5セル選択

B2セルD5セルが選択されました。

このように、UsedRangeプロパティを使うと、使用されている「最初のセルから最後のセル」のセル範囲が取得されます。

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

それでは、UsedRangeプロパティを使った表の取得方法をご紹介します。

実際にどのような動きをするか見てみましょう。
表
このような表にUsedRangeプロパティを実行してみます。

UsedRange表

表全体の範囲が取得されました!
このように、ワークシートを指定してUsedRangeプロパティを使うだけで、表全体の範囲が取得できます。
とても便利ですね!

表に空白行がある場合

それではCurrentRegionプロパティではうまく取得することができなかった、空白行がある表の場合を見てみましょう。
表空白あり

UsedRange表2

CurrentRegionプロパティではうまく取得することができませんでしたが、UsedRangeプロパティでは空白行があっても表全体の範囲を取得することができました!

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

1行目と1列目が空白の場合でもUsedRangeプロパティを使えば、簡単に取得することができます。
表空白行列ある

UsedRange表3
B2セルD6セルが選択されました。
使用されている最初のセルから最後のセルのセル範囲が取得されるため、このように1行目と1列目が空白が入っていても、正しく取得することができます。

罫線が引かれている表の場合

このように大変便利なUsedRangeプロパティですが、気をつけなければいけない点もあります。

表罫線
このように、あらかじめ表に罫線が引かれている場合もあると思います。

そのような場合に、UsedRangeプロパティを使うと、どのセル範囲を取得するかを見てみましょう。

UsedRange表4

このように、罫線が引かれている範囲すべてが選択されました。
これは「罫線が引かれている=使用されている」セルということです。
そのため、罫線が引かれている最後のセルが、UsedRangeプロパティのセル範囲の対象となってしまうのです。

また、表に関数が入力されているセルも同様に使用されているセルとなりますので、気をつけてください。

まとめ

今回は、シートで使用しているセル範囲全てをUsedRangeプロパティを使って取得する方法についてご紹介しました。

はじめに、前回ご紹介したCurrentRegionプロパティの使い方を説明しました。

CurrentRegionプロパティで取得できる表の範囲は、Rangeオブジェクトのアクティブセル(=空白行と空白列で囲まれたセル範囲)なので、空白行と空白列がある場合はうまく取得することができませんでした

しかし、UsedRangeプロパティでは指定されたワークシートで使用されているセル範囲を取得できるため、CurrentRegionプロパティでは取得できなかった空白行がある表も取得することができました

ただ、UsedRangeプロパティでは罫線が引かれているセルや関数が入力されているセルを取得してしまうため、その点には気をつける必要があります。

連載目次:エクセル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、ファイナンシャルプランナーなど、独学で取得してます。