【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法

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


Endプロパティ画像

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

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

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

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

今回は、Endプロパティを使って表の最終行番号を取得する方法をご紹介したいと思います。 今回ご紹介するEndプロパティを使用することで、簡単に表の最終行を取得することができます

罫線を引かれている表など、UsedRangeプロパティを使ってもうまく範囲を取得できない場合などに、このEndプロパティを使用することで、表の最終行を取得することができます。

UsedRangeプロパティの説明

まず、前回のおさらいからです。

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

Worksheetオブジェクト.UsedRange

使用されているセルの範囲全てを取得する方法

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

UsedRange表

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

ワークシートを指定してUsedRangeプロパティを使うだけで、表全体の範囲が取得できます。

罫線が引かれている表の範囲を取得する場合

表全体の範囲が簡単に取得できる、大変便利なUsedRangeプロパティですが、気をつけなければいけない点もあります。
表罫線
このように、あらかじめ表に罫線が引かれている場合もあると思います。

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

UsedRange表4

罫線が引かれている範囲すべてが選択されました。

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

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

Endプロパティを使って表の最終行番号を取得する方法

ここからは、Endプロパティを使って表の最終行番号を取得する方法をご紹介します。

Rangeオブジェクト.End(Direction)

Endプロパティを使うことで、データが入力されている範囲の終端セルを取得することできます。

Endプロパティの説明

このEndプロパティは引数(Direction)を指定する必要があります。
引数を指定することで、任意のセルから、どの方向の終端セルを取得するかを決めることができるのです。

引数 方向
xlDown
xlUp
xlToLeft
xlToRight

Endプロパティの実行例言葉での説明だけでは、少し難しいと思うので実際の動きを見てみましょう。
表罫線

このような表で実行してみます。

Endプロパティ例
ここで指定した引数[xlDown]は、下端のセルを取得する引数です。
指定したA1セルからデータが入力されている範囲の下端のセル、A6セルが指定されました。

これは、Excelのショートカット機能にある、Ctrlと同じです。Excel上で任意のセルを選択し、Ctrl+方向キーを押すことで、データが入力されている範囲の終端セルまで移動することができます。Excelを操作する上でも、大変便利なショートカットキーです。

それではもうひとつ実行してみます。

Endプロパティ例2

ここで指定した引数[xlToRight]は、右端のセルを取得する引数です。

指定したA1セルから右端のセル、D1セルが指定されました。
この他にも、引数には[xlUp][xlToLeft]の2つがあり、上下左右4つの引数を指定することができます。

表の最終行番号を取得する方法

Endプロパティで表の最終行番号を取得する方法Endプロパティを使って表の最終行番号を取得する方法には、大きく分けて2つあります。

ひとつは、先ほど説明した表の最初のセル(ここではA1セル)から下端セルを取得する方法です。

もうひとつの方法が、エクセルシートの最後のセルから上端セルを取得する方法です。

上に挙げた例では、表の最終行セルをSelectメソッドで指定していましたが、行番号はRowプロパティで取得することができます。

Rangeオブジェクト.Row

実際の例で説明します。

下の例はA2セルの行番号を取得しメッセージ画面で表示するコードです。

行番号表示

行番号「2」が取得されました。

最初のセル(A1セル)から表の最終行番号を取得する方法

それでは、最初のセル(A1セル)から表の最終行番号を取得する方法です。

それがこちらのコードです。

行番号表示2

表の最終行番号を取得することができました!

最後のセルから表の最終行番号を取得する方法

それでは次に、エクセルシートの最後のセルから表の最終行番号を取得する方法です。

または

行番号表示2

これで、表の最終行番号できました。

ここで使った、Rows.Countはシートの行数を取得します。
シートの行数はExcel2007以降なら1048576、2003以前なら65536です。
シートの最終行から、上方向に進んでいき、最初にデータが入力されているセルを指定します。

この方法ならば、空白行がある表でも、データが入力されている最終行番号を取得することができます。

まとめ

今回は、Endプロパティを使って表の最終行番号を取得する方法についてご紹介しました。

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

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

また、Endプロパティを使うことで、簡単に表の最終行を取得することができました。罫線を引かれている表など、UsedRangeプロパティを使ってもうまく範囲を取得できない場合などに、このEndプロパティを使用することで、表の最終行を取得することができます。

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