【Excel VBA】シート上の表の最後が何行目かを特定する方法

はじめに

Excel VBA マクロで、シート上の表の各レコードについて何らかの処理をする場合が良くあると思います。

例えば、以下のような表があったとします。

上の表の No.1 から No.6 までについて何らかの処理を行う場合、 for 文を使用して以下のようにコード化すると思います。

    For i = 4 To 9
        'i 行目に対する処理
    Next

レコードが追加・削除されることが無い場合はこれでも良いのですが、レコードが追加・削除される場合は、その度にコードの修正を行う必要があります。これではあまり良くありません。

以下では、レコードの追加・削除にも対応できるよう、表の最後が何行目なのかをコードの中で特定する方法について説明します。

[Ctrl + 矢印キー] の活用

Excel では、シート上で Ctrl + 矢印キー を押下すると、以下のようなルールでカーソルが移動するというショートカットがあります。

• 選択中のセルが空白の場合
  • 矢印キー方向のセルの中で、一番最初に値が設定されているセルまでカーソルが移動
• 選択中のセルに値が設定されている場合
  • 矢印方向の隣接しているセルが空白の場合
    • 矢印キー方向のセルの中で一番最初に値が設定されているセルまでカーソルが移動
  • 矢印方向の隣接しているセルに値が設定されている場合
    • 矢印キー方向のセルの中で一番最初の空白セルの一つ手前のセルまでカーソルが移動

例を一つ以下に示します。

この Ctrl + 矢印キー は VBA においても再現することができます。このショートカットを利用して表の最終行を特定することができます。

VBA での構文は以下の通りです。

表の最終行を特定する方法

上に記載した Ctrl + 矢印キー を活用して表の最終行を特定するための手順は以下の通りです。

1. シート上の表が含まれる列の一番下の行にあるセルに注目する
2. 1. のセルに対して Cells(Rows.Count, <列番号>).End(xlUp) を実行する
   • Rows.Count で、シートの一番最後の行番号を指定できます
3. 2. の結果選択されたセルの行番号を取得する（これが表の最後の行番号になる）
   • Cells(<行番号>, <列番号>).Row で対象セルの行番号を取得できます

わかりやすいように 3 ステップに分けて記載しましたが、実際には 1 行のコードで記述できます。

構文は以下のようになります。

• Cells(Rows.Count, <列番号>).End(xlUp).Row

使用例

以下の表の最終行の行番号を取得します。

表が含まれる 2 列目（B列）の最終行のセルから Ctrl + ↑ を実行して表の最終行を取得します。

コード例は以下の通りです。

Sub main()
    Dim lastRow#
    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    MsgBox lastRow
    
End Sub

実行結果は以下の通りです。

表の最後の行である 9 行目を取得できました。

あとは、以下のように取得した行番号を for 文 で使用すれば OK です。

Sub main()
    Dim lastRow#
    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 4 To lastRow
        'i行目に対する処理
    Next
    
End Sub

以上です。

あわせて読みたい

Excel VBA マクロ作成用情報まとめ 【初学者向け情報】 【Excel VBA】初心者からマクロを作成できるようになるまでの学習ロードマップ 【Excel VBA マクロ独学者・初心者向け】VBA 学習の第一歩【このペー...