きなこSHOW

VBAと日々の戯言

Excel VBAで最終行を取得するサンプルを紹介します

Excelで事務処理系の自動化ツールを作っていますが、データが入力されている最終行とか最終列を取得したい時ってけっこうあります。
毎回コード書くのも面倒なので、汎用的に使えるプロシージャを作って使いまわしています。やりたい事はほぼカバーできている「最終行取得function」です。
コピーしてそのまま使えるサンプルを紹介します!

Excelでの転記を自動で行うツールでは、行数がいつも固定の場合は必要ないのですがそうではない時の方が多く、データが入力されている最終のセル、行を検出するvbaコードの出番はかなり多いです。
最終セルを検出する方法はいろいろありますが、それぞれに一長一短があって開発者を悩ませます。
特性を理解したうえでその場に応じた適切な方法を、となるとなかなか難しいものです。

今回紹介するサンプルは、以下の用途に対応しています。

  1. 空白が含まれない表の最終行を取得したい
  2. 空白が含まれない=データがすべて入力されている特定の列の最終行を取得したい
  3. 空白は含まれてるけど特定列の最終行を取得したい
  4. 空白は含まれてるけど全体的に最終行を取得したい

「1」「4」は簡単ですよね。「2」「3」で悩むケースが多いかと思います。
これから紹介するサンプルは、1つのプロシージャですべてのケースに対応しています。

データが入力されている最終行を取得するサンプル

まず次のプロシージャを標準モジュールにコピペします。

'**************************************************
'名称 :F_GetRowMax    最大行位置を取得する
'引数 :robjSheet      (I ) Worksheet
'   :vlColNo        (I ) Long:KEYとなる列(あれば)
'   :rlRowMax       ( O) Long:(返却用)最大行
'   :rsMsg          ( O) String:エラーメッセージ
'戻り値:True=成功, False=どこかでエラーになった
'作成 :Kinaccco
'**************************************************
Public Function F_GetRowMax(ByRef robjSheet As Worksheet _
                  , Optional ByVal vlColNo As Long = 0 _
                  , Optional ByRef rlRowMax As Long = 0 _
                  , Optional ByRef rsMsg As String = "") As Boolean
On Error GoTo Sub_Err
Dim lbRet           As Boolean
Dim lbHit           As Boolean
Dim lobjURange      As Range
Dim llRowMax        As Long
Dim llRowMaxURange  As Long
Dim llColMaxURange  As Long
Dim i As Long, j As Long

    '--- 初期値セット ---
    lbRet = False
    rlRowMax = 0
    llRowMax = 0
    rsMsg = ""
    
    '--- パラメータチェック ---
    If robjSheet Is Nothing Then
        rsMsg = "[ERR-01]Worksheet参照の受け渡しに失敗しました"
        GoTo Sub_Exit
    End If
    
    '--- UsedRange取得 ---
    Set lobjURange = robjSheet.UsedRange
    
    '--- UsedRangeから最終行・最終列を取得する ---
    llRowMaxURange = lobjURange.Row + lobjURange.Rows.Count - 1
    llColMaxURange = lobjURange.Column + lobjURange.Columns.Count - 1
    
    If vlColNo = 0 Then
    
        '--- 基準列が省略された場合、UsedRange最終行列から逆順に総なめ ---
        lbHit = False
        With robjSheet
            For i = llRowMaxURange To 1 Step -1
                For j = llColMaxURange To 1 Step -1
                    If Len(Trim(.Cells(i, j).Value)) > 0 Then
                        lbHit = True
                        llRowMax = i
                        Exit For
                    End If
                Next j
                If lbHit Then Exit For
            Next i
            If lbHit = False Then
                rsMsg = "[ERR-02]最終行の取得に失敗しました"
                GoTo Sub_Exit
            End If
        End With
    Else
    
        '--- 基準列がUsedRange最終列より大きい場合エラーとする ---
        If vlColNo > llColMaxURange Then
            rsMsg = "[ERR-03]基準列に指定された値が正しくありません(最終列より大きい)"
            GoTo Sub_Exit
        End If
        
        With robjSheet
        
            If Len(.Cells(llRowMaxURange, vlColNo).Value) > 0 Then
            
                '--- UsedRange最終行の基準列に値が入力されている場合、UsedRange基準列の最終行を返す ---
                llRowMax = llRowMaxURange
            
            Else
            
                '--- UsedRange最終行の基準列が空白の場合、シート最終行から終端セルを返す ---
                llRowMax = .Cells(.Rows.Count, vlColNo).End(xlUp).Row
            End If
        End With
    End If
    
    '--- 返却用変数に格納する ---
    rlRowMax = llRowMax
    
    '--- 正常終了 ---
    lbRet = True

Sub_Exit:
On Error Resume Next

    Set lobjURange = Nothing
    
    F_GetRowMax = lbRet
        
    Exit Function

Sub_Err:
    rsMsg = "予期せぬエラーが発生しました" & vbCrLf & _
        "プロシージャ名:F_GetRowMax" & vbCrLf & _
        "エラー番号:" & Err.Number & vbCrLf & _
        "エラー内容:" & Err.Description
    GoTo Sub_Exit

End Function

サンプルコードの使用はすべて自己責任でお願いいたします。
次回は最大列を取得する関数の紹介とともに、このサンプルの呼び出し方法を説明します。

kinaccco.hatenadiary.com