Excel VBAテーブルの最終列、最終行、および右下隅を取得するについて

VBAマクロプログラムを使用して、Excelテーブルの最終列、最終行、または右下隅を取得し、データの範囲を見つけます。

VBAマクロプログラムを開発する場合、Excelワークシートのデータの範囲を見つけるのはよく遇う情況です。例えば、VBAプログラムを使用して、次のテーブルの最終行と最終列を自動的に取得します。

次にはVBAでデータ範囲を見つけるいくつかの方法を説明します。

Range.End プロパティ

Range.Endプロパティは、Ctrl +方向キーのショートカットキーと似ています。次のコードは、先頭列の一番下のセルから、上方向にデータがあるセルを検索します。

Dim rowNum As Long
rowNum = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "1列目の最後：" & rowNum

そして、このセルの位置に基づいてデータの1列目の最後の位置を取得します。

結果は次になります。

Range.Endプロパティを使用して、1行目の最後の位置も取得できます。使い方は同じですが、ただ方向が異なります。

Dim colNum As Long
colNum = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "1行目の最後：" & colNum

上記のこのセルの位置に基づいてデータの1行目の最後の位置を取得します。

結果は次になります。

Range.End関数のメリットは、構文が簡単で理解しやすいことですが、デメリットは、データ範囲が単一の行または単一の列に基づいてしか取得できません。データの範囲があまり整っていない場合(一部のセルに空白である場合)、この使い方を使用すると、問題が発生します。

Range.Findメソッド

Range.Findメソッドは、Excelの検索と置換機能に似ています。

次のVBAマクロコードは、セルA1から上方向に検索し(セルA1は左上隅の最初のセルであるため、上方向に検索すれば、右下隅のセルからです)、データを含むセルを検索します。

Dim rowNum As Long
rowNum = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
MsgBox "1列目の最後：" & rowNum

次には、これらの引数を説明します。

• What:=”*”：データが含むセルを検索します。
• After:=Range(“A1”)：セルA1の後から検索します。
• LookAt:=xlPart：セル内任意のデータを検索します。
• LookIn:=xlFormulas：数式を検索します(数式の計算結果が空白であっても、データがあるとします)
• SearchOrder:=xlByRows：列の方向に検索します。(水平方向に検索します)
• SearchDirection:=xlPrevious：前に検索します。(右から左へ、下から上へ)
• MatchCase:=False：大文字と小文字は区別されません。

Range.Findの引数の使い方は少し複雑ですが、慣れると、非常に便利です。 実行の結果は次のようになります。

Range.Findを使用して1行目の最後の位置を取得する方法も同じです。

Dim colNum As Long
colNum = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
MsgBox "1行目の最後：" & colNum

結果は次になります。

Range.Findメソッドのメリットは、データが整っていなくても、行全体または列全体を検索できます。デメリットは、引数が複雑すぎて、使い方に慣れるまで時間がかかります。

Range.SpecialCellsメソッド

Range.SpecialCellsメソッドの機能は、Ctrl + Endショートカットに似ています。テーブルの右下にすぐにジャンプしますが、Range.SpecialCellsメソッドがデータ範囲を検索する場合、[使用済み]セルも検索します。(現在は空白であっても、検索します。)これは上記の2つの方法とは異なります。

次にはRange.SpecialCellsメソッドを使用してテーブルの右下のセルを取得する例です。

With Range("A1").SpecialCells(xlCellTypeLastCell)
MsgBox "最後の列：" & .Row & vbNewLine & _
"最後の行：" & .Column
End With

Range.SpecialCellsは、直接データの右下隅にあるセルを検索するため、データの最終列と最終行を同時に取得できます。

結果は次になります。

以下は、右下隅のセルの位置を直接出力する例です。

Dim lastCell As Range
Set lastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
MsgBox "右下隅：" & lastCell.Address

結果は次になります。

Range.SpecialCells関数を使用して、使用されたセル範囲を取得できます。これは、ワークシートをクリアするときに役立ちますが、取得された範囲はには空白セルがある場合もあります。(この範囲は、ただワークブックが保存する場合にリセットします。)