みなさん、こんにちは。
なかじ(@nakaji098)です。
前回は、Endプロパティを使用して表の最終行を取得する方法をご紹介しました。
空白行がある表や罫線を引かれている表など、CurrentRegionやUsedRangeプロパティを使ってもうまく範囲を取得できない場合などに、このEndプロパティを使用することで、表の最終行を取得することができました。
今回は、前回から発展させて、Endプロパティで表全体の範囲を取得する方法をご紹介していきたいと思います。
Endプロパティを使えば、簡単に表の最終行を取得することができるので、それを少し応用して、Endプロパティを使って表全体の範囲を取得する方法をご紹介します。
Endプロパティで表の最終行を取得する方法
まずは、前回のおさらいからしていきたいと思います。
Endプロパティは、データが入力されている範囲の終端セルを取得することできます。
引数(Direction)を指定することで、任意のセルから、どの方向の終端セルを取得するかを決めることができるのです。
| 引数 | 方向 |
|---|---|
| xlDown | 下 |
| xlUp | 上 |
| xlToLeft | 左 |
| xlToRight | 右 |
最後のセルから表の最終行番号を取得する方法
表の最終行を取得する方法を説明していきます。
まず、行番号の取得はRowプロパティを使います。
それでは、最後のセルから表の最終行番号を取得する方法です。
この表を使って説明します。
それでは実際のコードです。
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です。
シートの最終行から、上方向に進んでいき、最初にデータが入力されているセルを指定します。
この方法ならば、空白行がある表でも、データが入力されている最終行番号を取得することができます。
前回は、最初の行（A1セル）から表の最終行を取得する方法も説明しましたが、今回はより実務でも使い勝手のよい、最後のセルから表の最終行番号を取得する方法を使って、この後の表全体の範囲を取得する方法を説明していきます。
Endプロパティで表全体の範囲を取得する方法
それでは、ここから今回のテーマである、Endプロパティで表全体の範囲を取得する方法について説明していきたいと思います。
表の右下端のセルを取得する方法
Endプロパティを使って表全体の範囲を取得するためには、表の右下端のセルを取得する必要があります。
表の右下端のセルを取得するにはこのようなコードになります。
Sub Endプロパティの実行例() Worksheets(1).Cells(Rows.Count,1).End(xlup).End(xlToRight).Select End Sub
D6セルを取得することができました。
解説しますと、表のA列最終セル（A6セル）を取得し、そこから右方向の終端セルを取得しました。
見てわかるように、Endプロパティは１つのコードの中で２回使えます。
表全体の範囲を取得する方法
それでは実際に表全体の範囲を取得します。
Sub 表の範囲取得()
Worksheets(1).Range("A1", Worksheets(1).Cells(Rows.Count,1).End(xlup).End(xlToRight)).Select
End Sub
表全体の範囲が取得されました！
でも、よくわかりませんね。。
ということで、すこし解説したいと思います。
まず、Rangeオブジェクトで表の範囲を直接指定する方法です。
Rangeプロパティの引数に表の範囲のアドレスを直接指定することで、表全体の範囲を選択できます。
今回の場合ですと、こうなります。
Sub Rangeオブジェクト表範囲()
Worksheets(1).Range("A1","D6").select
End Sub
このアドレスに指定するセルに、Endプロパティで取得セルを指定したものが今回の方法です。この場合ですと、D6セルに指定していたところを、Endプロパティを使ってセルを指定しました。
この方法ならば、空白行がある表や、罫線が引かれている表であっても、データが入力されている表全体の範囲を取得することができます。
コードの簡素化
このコードでも問題はありませんが、やはり見にくいので、少しすっきりさせたコードに変えてみます。
Withステートメント
Withステートメントを使って読みやすくしてみます。
Sub 表の範囲取得()
With Worksheets(1)
.Range("A1", .Cells(Rows.Count, 1).End(xlUp).End(xlToRight)).Select
End With
End Sub
これで少し見やすくなりましたね。
変数に格納
少し難しくなりますが、もっと見やすくすることもできます。
取得した右下端のセルをRangeオブジェクト変数に格納する方法です。
Sub 表の範囲取得()
Dim lastCell As Range
Set lastCell = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).End(xlToRight)
Worksheets(1).Range("A1", lastCell).Select
End Sub
解説しますと、変数lastCellに右下端のセルを格納します。
そして、変数lastCellを表の範囲のアドレスに指定します。
このとき、オブジェクトを変数に格納するので、Setステートメント使う必要があります。
まとめ
今回は、Endプロパティで表全体の範囲を取得する方法についてご紹介しました。
はじめに、前回ご紹介したEndプロパティを使用して表の最終行を取得する方法を説明しました。
Endプロパティを使うことで、データが入力されている表の最終行を簡単に取得することができました。
そして今回は、前回ご紹介した内容を発展させて、Endプロパティで表全体の範囲を取得する方法についてご紹介しました。
この方法を使うことで、空白行がある表や罫線が引かれている表であっても、データが入力されている表全体の範囲を取得することができます。
そして、コードが複雑になり読みにくい場合は、Withステートメントや変数を使うことで、より見やすいコードに変えることもできます。
連載目次：エクセルVBAでシートのセル範囲を簡単に取得する方法
エクセルVBAでシート上のセル範囲を取得。いつも必要になるのですが、これが意外と難しいと思われませんか？ 本シリーズではシート上のデータがあるセル範囲を簡単に、そして上手に取得する方法をお伝えしていきます。- 【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
- 【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法
- 【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法
- 【エクセルVBA】Endプロパティで表全体の範囲を取得する方法
