Excel VBAで最終行を取得する方法（Rows.Count+End(xlUp)とWorksheet.UsedRange.Rows.Countの使い方）

公開日：2017年5月6日

Excel VBAでは、For～Nextステートメントなど、データの最初の行から最終行まで繰り返し処理を使用するケースは多いですが、最終行の取得には様々な方法があります。

私がよく使う方法には次の2つがあります。
(1)Rows.CountとEnd(xlUp)を組み合わせる方法
(2)Wordsheet.UsedRange.Rows.Countを使用する方法

どちらもExcel最終行を取得するには便利な方法ですが、動作が微妙に異なるのでケースに応じて使い分けをしています。

次のExcelデータがあったとして、最終行を取得する場合を想定します。

1.Rows.CountとEnd(xlUp)を組み合わせる方法

Sub 最終行の取得()

    Dim 最終行 As Long

    最終行 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    MsgBox "最終行は " & 最終行 & "です。"

End Sub

2.Worksheet.UsedRange.Rows.Countを使用する方法

Sub 最終行の取得()

    Dim 最終行 As Long

    最終行 = ActiveSheet.UsedRange.Rows.Count

    MsgBox "最終行は " & 最終行 & "です。"

End Sub

VBAコードを実行すると、どちらの方法であっても8行目を取得することができます。

3.使い分けが必要になるケース

3-1.列によって最終行が異なるケース

Excelデータが次のような場合には、どうなるでしょう。

この場合、「1.Rows.CountとEnd(xlUp)を組み合わせる方法」では、3行目のCells(Rows.Count,1)の部分で列を指定することができるため、各列の最終行を取得することができます（Cells(Rows.Count,1)の1が列数に該当するので、この部分を最終行を取得したい列数に変えます。C列ならば3に変えれば最終行は9行目,D列ならば4に変えれば、最終行は7行目を取得することができます）。

これに対して、「2.Worksheet.UsedRange.Rows.Countを使用する方法」ではUsedRangeはWorksheetオブジェクトのプロパティであるため、セルの指定はできません。「ワークシート全体で使用した最終行」を取得することになり、C列の9行目が該当するため、9行目を取得することになります。

3-2.Null値のセルが存在するケース

Excelデータが次のようにNull値（空白）のセルが存在する場合には、どうなるでしょう。

「1.Rows.CountとEnd(xlUp)を組み合わせる方法」によると、3行目のコードのうち、最後のRowプロパティは値の取得のみ可能です。従って、最終行が仮にNull値である場合には、最終行は取得されず、値の入った最終行を取得することになります（列数1（A列）を指定すれば5行目、列数4（D列）を指定すれば6行目を取得します）。

従って、1.の方法を使用する場合には、列数の指定には「必ず値を入力する」列を指定してあげます（データベースでいうところの主キーとなる列など）。

これに対して、「2.Worksheet.UsedRange.Rows.Countを使用する方法」ではNull値であり、書式のみのセルも使用セルとして認識します。従って、このケースでは8行目を取得することになります。

3-3.Excelデータを頻繁に手作業で書き換えするケース

この場合、「2.Worksheet.UsedRange.Rows.Countを使用する方法」によった場合には注意が必要です。

というのも、Worksheet.UsedRangeプロパティは値や書式を完全に削除しないと、見た目は削除されているような状態であっても使用セルとして認識してしまうからです。

例えば上記の図で8行目のA8セルからD8セル値を削除し、さらに書式を格子のリボンで「枠なし」に設定して枠を削除します。

その後、2.のコードを実行すると、

見た感じでは8行目は削除されている状態になっていますが、8行目が最終行として取得されてしまいます。

原因は「書式が完全に削除されていないから」です。「リボンで格子なしに設定する」操作をマクロで記録すると分かるのですが、「Border.LineStyleプロパティ」を使用しています。

Sub 枠なしの設定()
    Range("A8:D8").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

これだと書式は完全には削除されません。書式を完全に削除するには、「Range.ClearFormats メソッド」を使用します（値の削除は「Range.ClearContents メソッド」を使用）。

Sub 値と書式の削除()
    ActiveSheet.Range("A8:D8").ClearContents
    ActiveSheet.Range("A8:D8").ClearFormats
End Sub

若しくは「Range.Delete メソッド」を使用します。これらの方法であれば、2.の方法であっても最終行は7行目を取得することができます。

従って、手作業でデータの書き換えを行う場合には「2.Worksheet.UsedRange.Rows.Countを使用する方法」はお勧めできません。この場合には、「1.Rows.CountとEnd(xlUp)を組み合わせる方法」を使用します。

関連記事

書籍には載っていないことにも言及してコメント

Excel VBAツールの作り方|開発手法をプログラマーがコメント

アイディア次第で可能性が広がる

営業事務で使える便利なExcel VBA EUCツール例5選

VBAの人気やRPAとの需要争奪戦もコメント

VBAの仕事|将来性や現在の需要を具体的かつ合理的に解説

様々な視点でVBAの難易度をコメント

VBAというプログラミング言語の難易度は?|VBAプログラマーが解説

Topへ戻る