EXCEL VBA 最終行を取得する14の方法・様々な最終行の探し方・最終行を効率的に見つける！

2024年1月8日

EXCEL VBA 最終行を取得する14の方法・様々な最終行の探し方・最終行を効率的に見つける！

●はじめに

ExcelのVBA（Visual Basic for Applications）を使用する際、データがどこまで続いているかを知ることは、効率的なプログラミングの基本です。特に大規模なデータを扱う場合、シートの最終行を正確に特定することは、データの整理、分析、またはレポート作成において非常に重要です。VBAには、この最終行を見つけるための多数の方法が存在しますが、それぞれに特徴と適用するシナリオが異なります。本記事では、そのような方法を14種類紹介し、それぞれの処理内容、特徴、そして注意すべき点を解説します。

これらの方法は、End(xlUp)メソッドのようなシンプルなものから、FindメソッドやUsedRangeプロパティを用いた複雑なものまで多岐にわたります。それぞれの方法は、シートの特定の列や行、または特定のデータパターンに基づいて最終行を特定します。また、データの大きさや構造に応じて最適な方法を選ぶことが、処理速度や正確性に大きく影響します。

Excel VBAを使いこなすためには、これらの多様な手法を理解し、適切に適用することが不可欠です。この記事を通して、読者の皆さんがそれぞれの方法の特徴を把握し、自身のプロジェクトに最適な手法を選べるようになることを願っています。

●【EXCEL VBA表の最終行・最終列を取得については、下記を参照して下さい】

EXCEL VBA 表の最終行最終列の取得(Row Columns)・セルの最終行

●【Range.Rows プロパティ (Excel)については、下記を参照して下さい】（Microsoft社　様）】
https://learn.microsoft.com/ja-jp/office/vba/api/excel.range.rows

効率的なExcel VBAプログラミング：最終行を特定する14の方法・最終行特定の方法とその特徴

●プログラム説明　(サンプル①) [1. End(xlUp)メソッドを使用する]

1. End(xlUp)メソッドを使用する
処理内容: 最下行から上に向かって最初の非空白セルを探します。
特徴: シンプルで一般的に使われる方法です。
注意事項: 最初のセルが空白の場合、不正確な結果を返す可能性があります。

'
'
Sub 最終行①() 'End(xlDown)メソッドを使用する
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox LastRow
End Sub '
'
'

Sub 最終行①() 'End(xlDown)メソッドを使用する

Dim LastRow As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox LastRow

End Sub '

●プログラム説明　(サンプル⓶)「2. UsedRangeプロパティを使用する」

2. UsedRangeプロパティを使用する
処理内容: 使用されている範囲の最終行を取得します。
特徴: シート全体の使用状況に基づくため、実際にデータが存在する最終行を返します。
注意事項: シートの使用範囲が正しく更新されていない場合、誤った結果を返すことがあります。

'
'
Sub 最終行②() 'UsedRangeプロパティを使用する
    Dim LastRow As Long
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    MsgBox LastRow
End Sub
'
'

Sub 最終行②() 'UsedRangeプロパティを使用する

Dim LastRow As Long

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

MsgBox LastRow

End Sub

●プログラム説明　(サンプル③)「3. Findメソッドを使用する」

3. Findメソッドを使用する
処理内容: シートで最後の非空白セルを探し、その行番号を取得します。
特徴: 効率的で正確な方法です。
注意事項: データがない場合、エラーを引き起こす可能性があります。

'
'
Sub 最終行③() 'Findメソッドを使用する
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox LastRow
End Sub
'
'

Sub 最終行③() 'Findメソッドを使用する

Dim LastRow As Long

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

MsgBox LastRow

End Sub

●プログラム説明　(サンプル④)「4. xlCellTypeLastCellを使用する」

4. xlCellTypeLastCellを使用する。
処理内容: 最後に編集されたセルの位置を基に最終行を特定します。
特徴: 使いやすいが、常に最適な結果を得られるわけではありません。
注意事項: 最終行以降に書式が適用されている場合、誤った行を返す可能性があります。

'
'
Sub 最終行④() 'xlCellTypeLastCellを使用する
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox LastRow
End Sub
'

Sub 最終行④() 'xlCellTypeLastCellを使用する

Dim LastRow As Long

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑤)「CountA関数を使用する」

5. CountA関数を使用する
処理内容: 指定した列に非空白セルがいくつあるかをカウントします。
特徴: 空白でないセルの数に基づくため、一部のケースでは有効です。
注意事項: 連続したデータの場合にのみ正確な結果を返します。

'
'
Sub 最終行⑤() 'CountA関数を使用する
    Dim LastRow As Long
    LastRow = Application.WorksheetFunction.CountA(Columns(1))
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑤() 'CountA関数を使用する

Dim LastRow As Long

LastRow = Application.WorksheetFunction.CountA(Columns(1))

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑥)「ループを使用するFor…Nextを利用する」

6. ループを使用するFor…Nextを利用する
処理内容: 列を下に向かってループし、最初の空白セルの直前の行を最終行として取得します。
特徴: より制御可能ですが、大きなデータでは時間がかかることがあります。
注意事項: 完全に空白のセルが連続すると、その直前の行を最終行として判定します。

'
'
Sub 最終行⑥() 'ループを使用するFor…Nexｔを利用する。
    Dim LastRow, I As Long
    For I = 1 To Rows.Count
    If IsEmpty(Cells(I, 1)) Then
        LastRow = I - 1
        Exit For
    End If
    Next I
    MsgBox LastRow
End Sub
'

Sub 最終行⑥() 'ループを使用するFor…Nexｔを利用する。

Dim LastRow, I As Long

For I = 1 To Rows.Count

If IsEmpty(Cells(I, 1)) Then

LastRow = I - 1

Exit For

End If

Next I

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑦)「Offsetメソッドを使用する」

7. Offsetメソッドを使用する
処理内容: 最終行に移動し、そこから上に向かって最終行を探します。
特徴: 他の方法と組み合わせて使用することが多いです。
注意事項: 正確な行数を知っている必要があります。

'
'
Sub 最終行⑦() 'Offsetメソッドを使用する
    Dim LastRow As Long
    LastRow = Cells(1, 1).Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑦() 'Offsetメソッドを使用する

Dim LastRow As Long

LastRow = Cells(1, 1).Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑧)「CurrentRegionプロパティを使用する」

8. CurrentRegionプロパティを使用する
処理内容: A1セルから始まる連続した範囲の行数を数えます。
特徴: A1セルから始まるデータに適しています。
注意事項: 他の範囲にデータがある場合、それを考慮しません。

'
'
Sub 最終行⑧() 'CurrentRegionプロパティを使用する
    Dim LastRow As Long
    LastRow = Range("A1").CurrentRegion.Rows.Count
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑧() 'CurrentRegionプロパティを使用する

Dim LastRow As Long

LastRow = Range("A1").CurrentRegion.Rows.Count

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑨)「Do…Loopを使用する」

9. Do…Loopを使用する
処理内容: 列を下に向かってループし、最初の空白セルの直前の行を最終行として取得します。
特徴: 単純でわかりやすいが、大きなデータセットでは時間がかかることがあります。
注意事項: 完全に空白のセルが連続すると、その直前の行を最終行として判定します。

'
'
Sub 最終行⑨() 'Do...Loopを使用する
    Dim LastRow, I As Long
    I = 1
    Do Until IsEmpty(Cells(I, 1))
        I = I + 1
    Loop
    LastRow = I - 1
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑨() 'Do...Loopを使用する

Dim LastRow, I As Long

I = 1

Do Until IsEmpty(Cells(I, 1))

I = I + 1

Loop

LastRow = I - 1

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑩)「Match関数を使用する」

10. Match関数を使用する
処理内容: 指定した列で最後の非空白セルの位置を見つけます。
特徴: 効率的で正確ですが、数値データに限定されます。
注意事項: 文字列や日付など他のデータ型には適用できない可能性があります。

'
'
Sub 最終行⑩() 'Match関数を使用する

    Dim LastRow, I As Long
    LastRow = Application.WorksheetFunction.Match("*", Range("A:A"), -1)
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑩() 'Match関数を使用する

Dim LastRow, I As Long

LastRow = Application.WorksheetFunction.Match("*", Range("A:A"), -1)

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑪)「For Eachループを使用する」

11. For Eachループを使用する
処理内容: 指定した列をループして最初の空白セルを見つけ、その1つ前のセルを最終行として使用します。
特徴: より直感的で理解しやすいですが、処理速度は遅いことがあります。
注意事項: データが不連続の場合、誤った結果を返す可能性があります。

'
'
Sub 最終行⑪() 'For Eachループを使用する
    Dim rng As Range, LastRow As Long
    For Each rng In Range("A:A")
        If rng.Value = "" Then
            LastRow = rng.Row - 1
            Exit For
        End If
    Next rng
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑪() 'For Eachループを使用する

Dim rng As Range, LastRow As Long

For Each rng In Range("A:A")

If rng.Value = "" Then

LastRow = rng.Row - 1

Exit For

End If

Next rng

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑫)「Evaluate関数を使用する」

12. Evaluate関数を使用する
処理内容: Excelの数式をVBAで評価し、特定の列の非空白セルの最大行番号を計算します。
特徴: 複雑な数式を使って高度な計算が可能です。
注意事項: 数式が複雑すぎると、理解やデバッグが難しくなる可能性があります。

'
'
Sub 最終行⑫() 'Evaluate関数を使用する
    Dim LastRow As Long
    LastRow = Evaluate("MAX(ROW(A:A)*(A:A<>""""))")
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑫() 'Evaluate関数を使用する

Dim LastRow As Long

LastRow = Evaluate("MAX(ROW(A:A)*(A:A<>""""))")

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑬)「AutoFilterメソッドを使用する」

13. AutoFilterメソッドを使用する
処理内容: 非空白セルをフィルタリングし、その後最終行を見つけます。
特徴: データがフィルタリングされた状態での最終行を見つけるのに適しています。
注意事項: フィルタリング後のデータのみを考慮するため、全てのデータを考慮したい場合には適していません。

'
'
Sub 最終行⑬() 'AutoFilterメソッドを使用する
    Dim LastRow As Long
    Range("A:A").AutoFilter Field:=1, Criteria1:="<>"
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    MsgBox LastRow
End Sub
'
'

Sub 最終行⑬() 'AutoFilterメソッドを使用する

Dim LastRow As Long

Range("A:A").AutoFilter Field:=1, Criteria1:="<>"

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

ActiveSheet.AutoFilterMode = False

MsgBox LastRow

End Sub

●プログラム説明　(サンプル⑭)「Range.Resizeメソッドを使用する」

14. Range.Resizeメソッドを使用する
処理内容: 指定されたセルから始まる範囲を再設定し、その行数を取得します。
特徴: 特定の範囲に限定されたデータに適しています。
注意事項: 範囲が正しく設定されていないと、誤った結果を返す可能性があります。

'
'
Sub 最終行⑭() 'Range.Resizeメソッドを使用する
    Dim LastRow As Long
    LastRow = Range("A1").Resize(Range("A1").CurrentRegion.Rows.Count, 1).Rows.Count

    MsgBox LastRow
End Sub
'
'