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データがあったとして、最終行を取得する場合を想定します。

ExcelVBA Rows.Count,UsedRange

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行目を取得することができます。

ExcelVBA Rows.Count,UsedRange

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

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

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

ExcelVBA Rows.Count,UsedRange

この場合、「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行目を取得することになります。

ExcelVBA Rows.Count,UsedRange

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

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

ExcelVBA Rows.Count,UsedRange

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

ExcelVBA Rows.Count,UsedRange

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

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

ExcelVBA Rows.Count,UsedRange

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

ExcelVBA Rows.Count,UsedRange

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

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

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

ExcelVBA Rows.Count,UsedRange

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

ExcelVBA Rows.Count,UsedRange

見た感じでは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)を組み合わせる方法」を使用します。