みなさん、こんにちは。
なかじ(@nakaji098)です。
これまで表の範囲を取得する方法をいくつかご紹介してきました。
今回は、表の最終行・最終列を取得する方法を紹介していきます。
表の最終行・最終列を取得する方法は、表の範囲を取得する方法と同じように複数あります。
それぞれの方法に特徴があり、表によって使い分ける必要があります。
ここでは３つの方法を紹介します。
- Endプロパティ
- CurrentRegionプロパティ
- UsedRangeプロパティ
Endプロパティを使って取得する方法
このEndプロパティを使う方法が、最終行・列を取得する方法としては、最も使われていると思います。
| 引数 | 方向 |
|---|---|
| xlDown | 下 |
| xlUp | 上 |
| xlToLeft | 左 |
| xlToRight | 右 |
Endプロパティを使って表の最終行を取得する方法は、以前の記事でもご紹介しましたので、参考にしてください。
表の最終行を取得
まずは、表の最終行を取得する方法です。
この表を使って説明していきます。
Sub Endプロパティの実行例() Msgbox Worksheets(1).cells(rows.count,2).end(xlup).Row End Sub
これで、表の最終行番号できました。
Rows.Countはシートの行数を取得します。
シートの行数はExcel2007以降は1048576行、Excel2003は65536行です。
シートの最終行から、上方向に進んでいき、最初にデータが入力されているセルの行を指定します。
なので、空白行がある表でも、データが入力されている最終行番号を取得することができます。
１列目が空白の場合は、上のコードのように２列目のセルを指定する必要があります。
表の最終列を取得
次は、表の最終列を取得する方法です。
Sub Endプロパティの実行例() MsgBox Worksheets(1).Cells(2, Columns.Count).End(xlToLeft).Column End Sub
このコードはシートの１行目が空白行の場合です。
１行目が空白の場合は、上のコードのように２行目を指定する必要があります。
２行目の最終列から左方向に移動し、最初にデータが入力されているセルの列を指定しています。
そのため、ここで９行目を指定しまうと正しい最終列が指定されません。
Sub Endプロパティの実行例() MsgBox Worksheets(1).Cells(9, Columns.Count).End(xlToLeft).Column End Sub
Endプロパティを使う場合には、どのセルを基準とするかが大事になります。
CurrentRegionプロパティを使って取得する方法
次は、CurrentRegionプロパティを使って表の最終行・最終列を取得する方法についてです。
このプロパティを使用することで、指定したRangeオブジェクトのアクティブセル（＝空白行と空白列で囲まれたセル範囲）を取得することができます。
Endでプロパティでは、基準に選んだセルの列が、最終列になります。
なので、上の例のように最終列にデータのないセルを基準にしてしまうと、うまく取得することができません。
このような場合には、CurrentRegionプロパティを使うことで簡単に取得することができます。
表の最終行を取得
まずは、最終行の取得からです。
この方法は、後で説明するUsedRangeプロパティと混同してしまうので気を付けてください。
この表を使います。
それでは実際のコードです。
Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)
MsgBox ws.Range("B2").CurrentRegion(ws.Range("B2").CurrentRegion.Count).Row
End Sub
最終行が取得できました。
コードについて、少し説明します。
使う人は少ないと思いますが、CurrentRegionプロパティには引数を指定することができます。
この引数には整数が指定され、その整数に対応したCurrentRegion内のセルを指定することができます。
セルの番号は、表の左上から右に1,2,3…となっております。
具体例をあげてみます。
Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)
ws.Range("B2").CurrentRegion(1).select
End Sub
引数に１を指定したので、表の一番左上のB2セルが選択されました。
このように引数に整数を指定することで、CurrentRegion内のセルを取得することができます。
そして、この引数にCurrentRegion.Countで取得した総セル数(上の例では28)が指定されることで、表の最終セルが取得されます。
その最終セルの行が、表の最終行になるのです。
表の最終列を取得
表の最終行が取得できれば、最終列を取得することは簡単です。
表の最終セルの行が最終行なので、最終セルの列が最終列になります。
Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)
MsgBox ws.Range("B2").CurrentRegion(ws.Range("B2").CurrentRegion.Count).Column
End Sub
最終列である５行目が取得されました。
空白行がある場合
簡単に表の最終行・列が取得できるCurrentRegionプロパティですが、空白行がある表の場合ですと、正しく取得することができません。
Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)
MsgBox ws.Range("B2").CurrentRegion(ws.Range("B2").CurrentRegion.Count).Row
End Sub
最終行を取得しようとすると、うまくできません。
CurrentRegionプロパティが、指定したRangeオブジェクトのアクティブセル（＝空白行と空白列で囲まれたセル範囲）を取得するためです。
空白行があると、空白行の前までがアクティブセルになります。
ですので、このような場合には、前に使ったEndプロパティか、これから紹介するUsedRangeプロパティを使う必要があります。
UsedRangeプロパティを使って取得する方法
UsedRangeプロパティは、指定されたワークシートで使用されているセル範囲を返します。
表の最終行を取得
それではUsedRangeプロパティを使って最終行を取得します。
先ほどの空白行のある表を使います。
Sub UsedRangeプロパティの実行例() Dim ws As Worksheet Set ws = Worksheets(1) MsgBox ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row End Sub
最終行を取得できました。
UsedRangeプロパティでは、CurrentRegionプロパティのように引数を指定することができません。
そこで、UsedRangeのRowsプロパティに、UsedRange.Rows.Countで取得した総行数を指定して、最終行を取得します。
少し複雑で、わかりづらいかもしれまんせんが、表がどのセルから始まっていても対応できるので便利な方法です。
ただし、UsedRangeプロパティは罫線が引かれている表ですと、罫線の引かれている行が最終行になってしまうので注意が必要です。
Sub UsedRangeプロパティの実行例() Dim ws As Worksheet Set ws = Worksheets(1) MsgBox ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row End Sub
罫線が引かれた行が最終行になっています。
このような表の最終行を取得したいときは、EndプロパティかUsedRangeプロパティを使ってください。
表の最終列を取得
最終列を取得する方法は、最終行とほぼ同じです。
Rowと指定していた箇所をColumnに変えるだけです。
Sub UsedRangeプロパティの実行例() Dim ws As Worksheet Set ws = Worksheets(1) MsgBox ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column End Sub
最終列が取得できました。
まとめ
今回は、表の最終行・最終列を取得する方法を紹介しました。
表の最終行・最終列を取得する方法は複数ありましたが、それぞれの方法に特徴があり、表によって使い分ける必要があることもわかっていただけたかと思います。
- Endプロパティ
- CurrentRegionプロパティ
- UsedRangeプロパティ
Endプロパティを使う方法は、最終行・列を取得する方法としては、最も使われていると思います。
変則的な表でない場合は、Endプロパティで最終行・列を取得すればいいと思います。
CurrentRegionプロパティを使う方法は、Endプロパティでうまく取得できない場合(最終列にデータのないセルがる表など)に使うといいと思います。このプロパティは、表の範囲を自動で取得できるので簡単で便利です。
しかし、空白行がある表などはうまく最終行・列を取得することができません。
UsedRangeプロパティを使う方法は、表がどのセルから始まっていても対応できるので便利な方法です。
ですが、罫線引かれている場合、罫線が引かれている範囲が表となりますので注意が必要です。
このようにプロパティごとに特徴があるので、実際に業務で表を扱うときは、使っている表に合ったプロパティを使ってください！
連載目次：エクセルVBAでシートのセル範囲を簡単に取得する方法
エクセルVBAでシート上のセル範囲を取得。いつも必要になるのですが、これが意外と難しいと思われませんか？ 本シリーズではシート上のデータがあるセル範囲を簡単に、そして上手に取得する方法をお伝えしていきます。- 【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
- 【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法
- 【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法
- 【エクセルVBA】Endプロパティで表全体の範囲を取得する方法
