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

お次は値が入力されている最終列、最大列といいますか、それを取得する関数を公開します。
前回紹介した最終行関数は呼び出し例もなく不親切だったので、呼び出し方法もあわせて紹介いたします。

まずはサンプルコードをご覧ください。

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

データが入力されている最終列を取得する関数、サンプルコードの使用はすべて自己責任でお願いいたします。

'**************************************************
'名称　：F_GetColMax    最大列位置を取得する
'引数　：robjSheet      (I ) Worksheet
'　　　：vlRowNo        (I ) Long：KEYとなる行(あれば)
'　　　：rlColMax       ( O) Long：(返却用)最大列
'　　　：rsMsg          ( O) String：エラーメッセージ
'戻り値：True=成功, False=どこかでエラーになった
'作成　：Kinaccco
'**************************************************
Public Function F_GetColMax(ByRef robjSheet As Worksheet _
                 , Optional ByVal vlRowNo As Long = 0 _
                 , Optional ByRef rlColMax 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 llColMax        As Long
Dim llColMaxURange  As Long
Dim llRowMaxURange  As Long
Dim i As Long, j As Long

    '--- 初期値セット ---
    lbRet = False
    rlColMax = 0
    llColMax = 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 vlRowNo = 0 Then
    
        '--- 基準行が省略された場合、UsedRange最終行列から逆順に総なめ ---
        lbHit = False
        With robjSheet
            For i = llColMaxURange To 1 Step -1
                For j = llRowMaxURange To 1 Step -1
                    If Len(Trim(.Cells(j, i).Value)) > 0 Then
                        lbHit = True
                        llColMax = 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 vlRowNo > llRowMaxURange Then
            rsMsg = "[ERR-03]基準行に指定された値が正しくありません(最終行より大きい)"
            GoTo Sub_Exit
        End If
        
        With robjSheet
        
            If Len(.Cells(vlRowNo, llColMaxURange).Value) > 0 Then
            
                '--- UsedRange最終列の基準行に値が入力されている場合、UsedRangeの最終列を返す ---
                llColMax = llColMaxURange
            
            Else
            
                '--- UsedRange最終列の基準行が空白の場合、シート最終列から終端セルを返す ---
                llColMax = .Cells(vlRowNo, .Columns.Count).End(xlToLeft).Column
            End If
        End With
    End If
    
    '--- 返却用変数に格納する ---
    rlColMax = llColMax
    
    '--- 正常終了 ---
    lbRet = True

Sub_Exit:
On Error Resume Next

    Set lobjURange = Nothing
    
    F_GetColMax = lbRet
        
    Exit Function

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

End Function

呼び出し例

それでは前回紹介した最終行と、今回の最終列取得関数の呼び出し方を説明します

    Dim objWB     As Workbook
    Dim objWS     As Worksheet
    Dim lngRowMax As Long
    Dim lngColMax As Long

    '--- Bookを開いてWorksheetの参照を取得する ---
    Set objWB = Workbooks.Open("C:Book1.xls")
    Set objWS = objWB.Worjsheets("Sheet1")
    
    '--- 最大行を取得する(基準となるKEY列が1列目の場合) ---
    If F_GetRowMax(objWS, 1, lngRowMax, strMsg) = False
        MsgBox strMsg
    Else
        MsgBox "最大行：" & Cstr(lngRowMax)
    End If

    '--- 最大行を取得する(基準となる列がない場合) ---
    If F_GetRowMax(objWS, , lngRowMax, strMsg) = False
        MsgBox strMsg
    Else
        MsgBox "最大行：" & Cstr(lngRowMax)
    End If
    
    '--- 最大列を取得する(基準となるKEY行が1列目の場合) ---
    If F_GetColMax(objWS, 1, lngColMax, strMsg) = False
        MsgBox strMsg
    Else
        MsgBox "最大列：" & Cstr(lngColMax)
    End If

    '--- 最大列を取得する(基準となる行がない場合) ---
    If F_GetColMax(objWS, , lngColMax, strMsg) = False
        MsgBox strMsg
    Else
        MsgBox "最大列：" & Cstr(lngColMax)
    End If

    '--- 後片付け ---
    Set objWS = Nothing
    objWB.Close SaveChanges:=False
    Set objWB = Nothing

クドい...ですかね、やはり。

たかだか最終行や最終列の取得なのにクドいコードと仰るなかれ。
クドいのには相応の理由があります。

まずこんな表があったとして、いくつかのケースで最終行を取得したいとします。

この表の最終行をUsedRangeを使って取得すると、15が返ります。
でも、UsedRangeなんかで取得できるほどウチの業務は単純じゃねんだよって方、多いと思います。
Excelの表はデータとしてだけでなく、印刷物を配布したりイントラに掲載したりして見るためにデータ以外の文字が入ってる場合もあったり、利用されるシーンはさまざまでしょう。
なんでもIT部門が主役というわけではない。ITワーカーのつらい所です。

データは増えたり減ったりして行数は毎回定まらないけど、合計の数式が入ってる最終行までは要らなくて、商品名が入ってる列の中で最大行が欲しいとき、この例では今のところ14行目となっている行(伝わります?)を動的に取得するために、基準となるKEY列を指定して「F_GetRowMax」を呼び出します。
すべての行で値が入力されているのはこの例では商品名なので、商品名が入っている列＝3列目を基準として最終行を求める指定方法は次のように記述します。

If F_GetRowMax(objWS, 3, lngRowMax, strMsg) = False Then
    …

F_GetRowMax関数では、最終行を検出したら結果を第3引数に格納して返します。

同様に最終列を取得する場合も、基準となる行が決まっていればその行を指定して「F_GetColMax」を呼びます。
例えば1行目に見出しがあるとしてその最終列を取得する場合は以下のように、基準行に1を指定します。

If F_GetColMax(objWS, 1, lngColMax, strMsg) = False Then
    …

F_GetColMax関数では、最終列を検出したら結果を第3引数に格納して返します。
上の表なら、4が返ります。

一方、値が必ず入っている行/列がない場合、どの行もどの列も基準にできないので、次のように基準行/基準列の指定を省略して関数を呼び出します。

If F_GetRowMax(objWS, , lngRowMax, strMsg) = False Then
    …

If F_GetColMax(objWS, , lngColMax, strMsg) = False Then
    …

この場合返ってくるのはUsedRangeの最終行と最終列です。

正しい値を取得できない？プロパティ・メソッド

多くのサイトで最終行・最終列の取得方法が紹介されていますが、要件を満たさないケースも多いのです。

.End(xlDown).Row
よく取り上げられている「Sheet.Range("A1").End(xlDown).Row」
これでは、A列で最終行までの間にどこか空白が含まれていたら、正しい最終行が取れません。
調べる列もA列じゃなくて可変にしたい。
.SpecialCells(xlLastCell).Row
それから「.Cells.SpecialCells(xlLastCell).Row」を使っているパターンもいまだに見かけますが、やめたほうがいい例です。
この「SpecialCells」は保護されたシートでは使えません。
私もかつては使ってましたが、利用部門から指摘されて使うのをやめました。
同様に「CurrentRegion」も保護されたシートでは使用できません。
業務上必要でシート保護することもあるでしょう。自分が知ってるステートメントを使いたいが為にビジネス要件を変えてしまっては本末転倒だと思うのです。
この2つを使用するときはこれらの事に注意する必要があります。