【エクセルVBA】表の最終行・最終列を取得する方法のまとめ

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

これまで表の範囲を取得する方法をいくつかご紹介してきました。

今回は、表の最終行・最終列を取得する方法を紹介していきます。

表の最終行・最終列を取得する方法は、表の範囲を取得する方法と同じように複数あります。
それぞれの方法に特徴があり、表によって使い分ける必要があります。

ここでは3つの方法を紹介します。

  • Endプロパティ
  • CurrentRegionプロパティ
  • UsedRangeプロパティ
スポンサーリンク

Endプロパティを使って取得する方法

このEndプロパティを使う方法が、最終行・列を取得する方法としては、最も使われていると思います。

Rangeオブジェクト.End(Direction)
引数 方向
xlDown
xlUp
xlToLeft
xlToRight

Endプロパティを使って表の最終行を取得する方法は、以前の記事でもご紹介しましたので、参考にしてください。

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

表の最終行を取得

まずは、表の最終行を取得する方法です。

この表を使って説明していきます。
vba_table

Sub Endプロパティの実行例() 
Msgbox Worksheets(1).cells(rows.count,2).end(xlup).Row 
End Sub

vba_table2

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

Rows.Countはシートの行数を取得します。
シートの行数はExcel2007以降は1048576行、Excel2003は65536行です。

シートの最終行から、上方向に進んでいき、最初にデータが入力されているセルの行を指定します。
なので、空白行がある表でも、データが入力されている最終行番号を取得することができます。

1列目が空白の場合は、上のコードのように2列目のセルを指定する必要があります。

表の最終列を取得

次は、表の最終列を取得する方法です。

Sub Endプロパティの実行例() 
MsgBox Worksheets(1).Cells(2, Columns.Count).End(xlToLeft).Column
End Sub

vba_table3

このコードはシートの1行目が空白行の場合です。
1行目が空白の場合は、上のコードのように2行目を指定する必要があります。

2行目の最終列から左方向に移動し、最初にデータが入力されているセルの列を指定しています。

そのため、ここで9行目を指定しまうと正しい最終列が指定されません。

Sub Endプロパティの実行例() 
MsgBox Worksheets(1).Cells(9, Columns.Count).End(xlToLeft).Column
End Sub

vba_table4

Endプロパティを使う場合には、どのセルを基準とするかが大事になります。

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

次は、CurrentRegionプロパティを使って表の最終行・最終列を取得する方法についてです。

Rangeオブジェクト.CurrentRegion

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

Endでプロパティでは、基準に選んだセルの列が、最終列になります。
なので、上の例のように最終列にデータのないセルを基準にしてしまうと、うまく取得することができません。

このような場合には、CurrentRegionプロパティを使うことで簡単に取得することができます

表の最終行を取得

まずは、最終行の取得からです。
この方法は、後で説明するUsedRangeプロパティと混同してしまうので気を付けてください。

この表を使います。
vba_table5

それでは実際のコードです。

Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)

MsgBox ws.Range("B2").CurrentRegion(ws.Range("B2").CurrentRegion.Count).Row

End Sub

vba_table6
最終行が取得できました。

コードについて、少し説明します。
使う人は少ないと思いますが、CurrentRegionプロパティには引数を指定することができます

CurrentRegion(引数)

この引数には整数が指定され、その整数に対応したCurrentRegion内のセルを指定することができます。
セルの番号は、表の左上から右に1,2,3…となっております。
vba_table8

具体例をあげてみます。

Sub CurrentRegionプロパティの実行例() 
Dim ws As Worksheet
Set ws = Worksheets(1)

ws.Range("B2").CurrentRegion(1).select

End Sub

vba_table7

引数に1を指定したので、表の一番左上の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

vba_table9

最終列である5行目が取得されました。

空白行がある場合

簡単に表の最終行・列が取得できるCurrentRegionプロパティですが、空白行がある表の場合ですと、正しく取得することができません。

vba_table

Sub CurrentRegionプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)

MsgBox ws.Range("B2").CurrentRegion(ws.Range("B2").CurrentRegion.Count).Row

End Sub

vba_table10

最終行を取得しようとすると、うまくできません。

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

ですので、このような場合には、前に使ったEndプロパティか、これから紹介するUsedRangeプロパティを使う必要があります。

UsedRangeプロパティを使って取得する方法

Worksheetオブジェクト.UsedRange

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

表の最終行を取得

それではUsedRangeプロパティを使って最終行を取得します。
先ほどの空白行のある表を使います。

vba_table

Sub UsedRangeプロパティの実行例()
Dim ws As Worksheet
Set ws = Worksheets(1)

MsgBox ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

End Sub

vba_table2

最終行を取得できました。
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

vba_table11

罫線が引かれた行が最終行になっています。
このような表の最終行を取得したいときは、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

vba_table3
最終列が取得できました。

まとめ

今回は、表の最終行・最終列を取得する方法を紹介しました。

表の最終行・最終列を取得する方法は複数ありましたが、それぞれの方法に特徴があり、表によって使い分ける必要があることもわかっていただけたかと思います。

  • Endプロパティ
  • CurrentRegionプロパティ
  • UsedRangeプロパティ

Endプロパティを使う方法は、最終行・列を取得する方法としては、最も使われていると思います。
変則的な表でない場合は、Endプロパティで最終行・列を取得すればいいと思います。

CurrentRegionプロパティを使う方法は、Endプロパティでうまく取得できない場合(最終列にデータのないセルがる表など)に使うといいと思います。このプロパティは、表の範囲を自動で取得できるので簡単で便利です。
しかし、空白行がある表などはうまく最終行・列を取得することができません。

UsedRangeプロパティを使う方法は、表がどのセルから始まっていても対応できるので便利な方法です。
ですが、罫線引かれている場合、罫線が引かれている範囲が表となりますので注意が必要です。

このようにプロパティごとに特徴があるので、実際に業務で表を扱うときは、使っている表に合ったプロパティを使ってください!

連載目次:エクセルVBAでシートのセル範囲を簡単に取得する方法

エクセルVBAでシート上のセル範囲を取得。いつも必要になるのですが、これが意外と難しいと思われませんか? 本シリーズではシート上のデータがあるセル範囲を簡単に、そして上手に取得する方法をお伝えしていきます。
  1. 【エクセルVBA】表が変更されても、表全体の範囲を簡単に取得する方法
  2. 【エクセルVBA】シートで使用されているセルの範囲全てを取得する方法
  3. 【エクセルVBA】Endプロパティを使って表の最終行番号を取得する方法
  4. 【エクセルVBA】Endプロパティで表全体の範囲を取得する方法
タイトルとURLをコピーしました