【Excel VBA】各列・各行をチェックし、最大の行・列番号を取得する方法

Excel VBAで、データ範囲の最終行・最終列の取得は、データ分析や自動処理において重要です。しかし、複数の行や列にわたって値が入力されている場合、単純な判定方法では適切な範囲を取得できないことがあります。

例えば、各列の最終行が異なり、指定した1つの列で判断できないようなケースです。

そこで、今回は各列・各行をチェックし、最大の行・列番号を取得する方法の事例です。

内容：

アウトプットイメージ
VBAサンプル
- 最終行・最終列の取得の基本
- サンプル例
  - 補足説明
活用事例

動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2504）です。

アウトプットイメージ

次のように空白を含む表で、どの行列で最終行・最終列を判定するのがよいかわからない場合に、各列・各行をチェックして最大の行・列番号を取得します。

この事例では、”Q”の位置が最終行で10行目、”S”の位置が最終列で5列目であることを取得します。

Qの位置が最終行で10行目、Sの位置が最終列で5列目であることを取得している — 各列・各行をチェックし、最大の行・列番号を取得

VBAサンプル

最終行・最終列の取得の基本

いくつかの方法はありますが、データ抽出や加工の業務では以下が基本です。

アクティブシートの1行目、および1列目の場合：

最終行 = Cells(Rows.Count, 1).End(xlUp).Row
最終列 = Cells(1, Columns.Count).End(xlToLeft).Column

この他に、書式設定された箇所を含めたUsedRangeプロパティを使用する方法もあります。しかし、データ抽出や加工の業務では値の有無で判断するケースが多いため、上記のEndプロパティが基本で良いと考えます。

サンプル例

上記の基本を踏まえ、各列・各行をチェックし、最大の行・列番号を取得するVBAのサンプルは次のとおりです。

Function 取得最終行列(対象シート As Worksheet) As Variant
    Dim 最終行 As Long, 最終列 As Long
    Dim 最大最終行 As Long, 最大最終列 As Long
    Dim 列番号 As Long
    
    最大最終行 = 0
    最大最終列 = 0
    
    ' 【最終行の取得】各列をチェックし、最大の行番号を取得'
    For 列番号 = 1 To 対象シート.UsedRange.Columns.Count
        最終行 = 対象シート.Cells(Rows.Count, 列番号).End(xlUp).Row
        If 最終行 > 最大最終行 Then
            最大最終行 = 最終行
        End If
    Next 列番号
    
    ' 【最終列の取得】各行をチェックし、最大の列番号を取得'
    For 列番号 = 1 To 対象シート.UsedRange.Rows.Count
        最終列 = 対象シート.Cells(列番号, Columns.Count).End(xlToLeft).Column
        If 最終列 > 最大最終列 Then
            最大最終列 = 最終列
        End If
    Next 列番号
    
    ' 配列として返す（最終行, 最終列）'
    取得最終行列 = Array(最大最終行, 最大最終列)
End Function

補足説明

この関数は、調べたい対象のシートを引数として渡すことで、配列としてArray(最終行, 最終列)の形式で値を取得します。

例えば、以下のコードを実行すると、アクティブなシートの最終行・最終列がメッセージボックスで表示されます（冒頭のアウトプットイメージ）。

Sub テスト取得最終行列()
    Dim シート As Worksheet
    Set シート = ActiveSheet
    
    Dim 結果 As Variant
    結果 = 取得最終行列(シート)
    
    MsgBox "最終行: " & 結果(0) & vbCrLf & "最終列: " & 結果(1)
End Sub