みなさん、こんにちは。
なかじ(@nakaji098)です。
前回は、UsedRangeプロパティを使ってシートで使用されているセルの範囲全てを取得する方法をご紹介しました
UsedRangeプロパティでは指定されたワークシートで使用されているセル範囲を取得できるため、CurrentRegionプロパティでは取得できなかった空白行がある表も取得することができました。
今回は、Endプロパティを使って表の最終行番号を取得する方法をご紹介したいと思います。 今回ご紹介するEndプロパティを使用することで、簡単に表の最終行を取得することができます。
罫線を引かれている表など、UsedRangeプロパティを使ってもうまく範囲を取得できない場合などに、このEndプロパティを使用することで、表の最終行を取得することができます。
UsedRangeプロパティの説明
まず、前回のおさらいからです。
UsedRangeプロパティは、指定されたワークシートで使用されているセル範囲を返します。
使用されているセルの範囲全てを取得する方法
実際にどのような動きをするか見てみましょう。
このような表にUsedRangeプロパティを実行してみます。
Sub 表の範囲を選択() Worksheets(1).UsedRange.Select End Sub
このように、UsedRangeプロパティを使うと、使用されている「最初のセルから最後のセル」のセル範囲が取得されます。
ワークシートを指定してUsedRangeプロパティを使うだけで、表全体の範囲が取得できます。
罫線が引かれている表の範囲を取得する場合
表全体の範囲が簡単に取得できる、大変便利なUsedRangeプロパティですが、気をつけなければいけない点もあります。
このように、あらかじめ表に罫線が引かれている場合もあると思います。
そのような場合に、UsedRangeプロパティを使うと、どのセル範囲を取得するかを見てみましょう。
Sub 表の範囲を選択() Worksheets(1).UsedRange.Select End Sub
罫線が引かれている範囲すべてが選択されました。
これは「罫線が引かれている＝使用されている」セルということです。
そのため、罫線が引かれている最後のセルまでが、UsedRangeプロパティのセル範囲の対象となってしまうのです。
また、表に関数が入力されているセルも同様に使用されているセルとなりますので、気をつけてください。
Endプロパティを使って表の最終行番号を取得する方法
ここからは、Endプロパティを使って表の最終行番号を取得する方法をご紹介します。
Endプロパティを使うことで、データが入力されている範囲の終端セルを取得することできます。
Endプロパティの説明
このEndプロパティは引数(Direction)を指定する必要があります。
引数を指定することで、任意のセルから、どの方向の終端セルを取得するかを決めることができるのです。
| 引数 | 方向 |
|---|---|
| xlDown | 下 |
| xlUp | 上 |
| xlToLeft | 左 |
| xlToRight | 右 |
Endプロパティの実行例言葉での説明だけでは、少し難しいと思うので実際の動きを見てみましょう。
このような表で実行してみます。
Sub Endプロパティの実行例()
Worksheets(1).Range("A1").End(xlDown).Select
End Sub
ここで指定した引数[xlDown]は、下端のセルを取得する引数です。
指定したA1セルからデータが入力されている範囲の下端のセル、A6セルが指定されました。
これは、Excelのショートカット機能にある、Ctrl＋↓と同じです。Excel上で任意のセルを選択し、Ctrl＋方向キーを押すことで、データが入力されている範囲の終端セルまで移動することができます。Excelを操作する上でも、大変便利なショートカットキーです。
それではもうひとつ実行してみます。
Sub Endプロパティの実行例()
Worksheets(1).Range("A1").End(xlToRight).Select
End Sub
ここで指定した引数[xlToRight]は、右端のセルを取得する引数です。
指定したA1セルから右端のセル、D1セルが指定されました。
この他にも、引数には[xlUp][xlToLeft]の２つがあり、上下左右４つの引数を指定することができます。
表の最終行番号を取得する方法
Endプロパティで表の最終行番号を取得する方法Endプロパティを使って表の最終行番号を取得する方法には、大きく分けて２つあります。
ひとつは、先ほど説明した表の最初のセル（ここではA1セル）から下端セルを取得する方法です。
もうひとつの方法が、エクセルシートの最後のセルから上端セルを取得する方法です。
上に挙げた例では、表の最終行セルをSelectメソッドで指定していましたが、行番号はRowプロパティで取得することができます。
実際の例で説明します。
下の例はA2セルの行番号を取得しメッセージ画面で表示するコードです。
Sub 行番号取得()
Msgbox Worksheets(1).Range("A2").Row
End Sub
行番号「２」が取得されました。
最初のセル（A1セル）から表の最終行番号を取得する方法
それでは、最初のセル（A1セル）から表の最終行番号を取得する方法です。
それがこちらのコードです。
Sub Endプロパティの実行例()
Msgbox Worksheets(1).Range("A1").End(xlDown).Row
End Sub
表の最終行番号を取得することができました！
最後のセルから表の最終行番号を取得する方法
それでは次に、エクセルシートの最後のセルから表の最終行番号を取得する方法です。
Sub Endプロパティの実行例()
Msgbox Worksheets(1).Range("A"& rows.count).end(xlup).Row
End Sub
または
Sub Endプロパティの実行例() Msgbox Worksheets(1).cells(rows.count,1).end(xlup).Row End Sub
これで、表の最終行番号できました。
ここで使った、Rows.Countはシートの行数を取得します。
シートの行数はExcel2007以降なら1048576、2003以前なら65536です。
シートの最終行から、上方向に進んでいき、最初にデータが入力されているセルを指定します。
この方法ならば、空白行がある表でも、データが入力されている最終行番号を取得することができます。
まとめ
今回は、Endプロパティを使って表の最終行番号を取得する方法についてご紹介しました。
はじめに、前回ご紹介したUsedRangeプロパティの使い方を説明しました。
UsedRangeプロパティでは指定されたワークシートで使用されているセル範囲を取得できます。ただ、UsedRangeプロパティでは罫線が引かれているセルや関数が入力されているセルを取得してしまうため、その点には気をつける必要があります。
また、Endプロパティを使うことで、簡単に表の最終行を取得することができました。罫線を引かれている表など、UsedRangeプロパティを使ってもうまく範囲を取得できない場合などに、このEndプロパティを使用することで、表の最終行を取得することができます。
連載目次：エクセルVBAでシートのセル範囲を簡単に取得する方法
エクセルVBAでシート上のセル範囲を取得。いつも必要になるのですが、これが意外と難しいと思われませんか？ 本シリーズではシート上のデータがあるセル範囲を簡単に、そして上手に取得する方法をお伝えしていきます。- 【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
- 【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法
- 【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法
- 【エクセルVBA】Endプロパティで表全体の範囲を取得する方法
