VBAで最終行を取得する

今回は前回の補足としてExcelVBAで
最終行を取得する動きについて詳しく説明をします。

上から下へ最終行を調べる

最終行を取得するにあたって、
上から下に行く動きと
下から上に行く動きがあります。

自分が体験した失敗談などを含めて
それぞれの動きについて紹介したいと思います。

上から下に向けて最終行を取得する場合

動きとしては指定のセル(B3)からCtrl＋↓を押した時に止まる場所のセルを取得します。

Sub 最終行を取得()

    '変数名"x"をVariant方式で指定します
    Dim x As Variant
    
    '変数"x"にB3からCtrl＋↓と同じ動作をして止まったセルの行数の値を取得
    x = Cells(3, 2).End(xlDown).Row
    
    '確認用のメッセージボックス
    MsgBox "取得した最終行は" & x & "行目です"

End Sub

実行結果

欠点としては、列の中に空白セルがあった場合、
そこが最終行とされる為、必ずしも最終行が取得できない場合があります。

本来25行目が最終行ですが、14行目に空白があるので、13行目で止まってしまっています。

どこから開始して、空白のセルがないかを確認する必要があるので
自分で管理していない表などでは注意が必要です。

下から上へ最終行を調べる

次に下から上に向かって最終行を調べる場合です。

動きとしては指定したセルの行目(B1048576)からCtrl＋↑を押した時に止まる場所のセルを取得します。
紹介するのはデータ上限の最終行から上に調べていき、
値が入っているセルの行数の値を取得しています。

Sub 最終行を取得()

    '変数名"x"をVariant方式で指定します
    Dim x As Variant
    
    '変数"x"にB1048576からCtrl＋↑と同じ動作をして止まったセルの行数の値を取得
    x = Cells(Rows.Count, 2).End(xlUp).Row
    
    '確認用のメッセージボックス
    MsgBox "取得した最終行は" & x & "行目です"

End Sub

実行結果

私は基本的にこの動作を多く適用しています。
一覧表であれば項番や管理ナンバーがふられている物が多く、
途中に穴抜けのセルがあっても最終行を取得しやすいからです。

14行目に空白があっても最終行は25行目と取得できています。

欠点としては、エクセルのバージョンによってデータ上の最終行が異なります。
・Excel2003までは65536行目
・Excel2007からは1048576行目

今はどこの現場でも2007以降を使っているとは思いますが、
最終行はファイル形式に準じています。

昔から稼働している現場だと拡張子"xls"の
「Excel 97-2003 ブック」で作成されたファイルがあるかもしれません。
うまく動かない場合はファイルの種類も確認してください。
解決策としては"Rows.Count"を"65536"にすればとりあえずは動きますｗ

VBAで最終列を取得する

最終列を調べる場合、
左から右に行く動きと
右から左に行く動きがあります。

左から右に最終列を調べる

最終列を調べる方法ですが、
まずは左から右に向かって最終列を調べる場合です。

動きとしては指定のセル(B3)からCtrl＋→を押した時に止まる場所のセルを取得します。

Sub 最終列を取得()

    '変数名"x"をVariant方式で指定します
    Dim x As Variant
    
    '変数"x"にB3からCtrl＋→と同じ動作をして止まったセルの行数の値を取得
    x = Cells(3, 2).End(xlToRight).Column
    
    '確認用のメッセージボックス
    MsgBox "取得した最終列は" & x & "列目です"

End Sub

実行結果

欠点や使い方については上から下に向けて動かすのとほぼ同じです。

このように空白があれば直前で止まり、目的の列ではないところで止まってしまいます。

右から左に最終列を調べる

最後に右から左に向かって最終列を調べる場合です。
動きとしては指定のセルからCtrl＋←を押した時に止まる場所のセルを取得します。

Sub 最終列を取得()

    '変数名"x"をVariant方式で指定します
    Dim x As Variant
    
    '変数"x"にXFD3(XFD列は16384列目)からCtrl＋←と同じ動作をして止まったセルの行数の値を取得
    x = Cells(3, Columns.Count).End(xlToLeft).Column
    
    '確認用のメッセージボックス
    MsgBox "取得した最終列は" & x & "列目です"

End Sub

実行結果

欠点や使い方については下から上に向けて動かすのとほぼ同じです。
手抜きなようですが、本当に同じなんですｗ

次回は対象キーワードを探して同じ列情報を取得してくるVLOOKUP関数を紹介します。
でもVBAはループ処理、IF分の文字一致等を説明します。