【VBA初心者】一番下のセルまで処理する

エクセルで「一番下まで同じ処理を繰り返したい」という場面はよくあります。同じ値を一番下までコピペしたい。データが何件あるのかを数えたい、など。VBAでどうすれば良いのかを紹介します。

特に、forループと組み合わせると便利に使うことができます。
初心者のうちは、「とりあえず100件くらいしか扱わないから、1000回くらいのループにしておけば大丈夫」などとやりがちですが、これはお勧めできません。
処理に無駄が多いうえ、忘れたころにバグの温床になりかねないからです。

VBAで一番下を特定する　基本編
1. 一番下の行番号（行アドレス）を取得する
2. 一番下の行の値を取得する
VBAで一番下を特定する　応用編　ログ記録機能を作る
1. 関数化してログ記録機能を作る
2. 実行
まとめ

VBAで一番下を特定する　基本編

一番下の行番号（行アドレス）を取得する

このような状態の「一番下」を求めてみます。いうまでもなく20行目が最終行です。

結論から言えばこうです。

Option Explicit

Sub sample_code()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    MsgBox (lastrow & "行目が最終です。")
End Sub

rows.count

エクセルのすべての行数のカウントです。つまり一番下の行のラベルに一致します。
下記のようにすることで、「A1048576セル」を表します。

Cells(Rows.Count, "A")

end(xlUp)

キー操作でいうところの「ctrl + ↑」にあたります。（ちなみにend(xldown)で「ctrl + ↓」です。）
下記のようにすることで、「A1048576セルから上に向かってデータが存在するセルまでジャンプ」を表します。

Cells(Rows.Count, "A").End(xlUp)

Cells(1,columns.Count).End(xltoleft)
としたら、1行目の最終列（のラベル）を求めることもできます。

row

rowは行ラベルを返します。下記のようにすることで「A1048576セルから上に向かってデータが存在するセルの行ラベル」を表します。

Cells(Rows.Count, "A").End(xlUp).Row

ちょっとおもしろいのは、下から上に向かって処理しているところです。
感覚的には上から下へ向かって「どうやって一番下を特定しようか」と考えるところですが、まず最下行を特定し、そこから上に向かってジャンプさせています。

この処理に慣れると、行を削除するマクロなど作った時にも便利な発想ができます。
というのは行を削除する際、下から上に向かって処理をすれば行ラベルがズレることなく処理ができるからです。

ちなみにVBAではなく操作で行いたい場合は下記の記事を参考にしてください。

一番下の行の値を取得する

行アドレスがわかっているので、このようにすれば最終行のセルの値も取得できます。

Option Explicit

Sub sample_code()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    MsgBox (Cells(lastrow, "A").Value & "です。")
End Sub

VBAで一番下を特定する　応用編　ログ記録機能を作る

関数化してログ記録機能を作る

処理の正常終了、異常終了をログに残す機能を作ってみます。

シート「log」を作る

プログラム的には必須ではありませんがユーザーにとって分かりやすくするため）

ログのタイトルを作る

シートのオブジェクト名をlogにする

オブジェクトブラウザでオブジェクト名を変更できます。（後述のコードを使うためには必須）

コードを記述する

logオブジェクトをWクリックし、その中にコードを記述します。
第一のポイントはfunction （関数）にしているところです。

第二のポイントは lastrow + 1としてwriterowを決めているところです。
これで「現在記入されている行の”次の行”」を常に求めることができます。

Option Explicit

Public Sub write_log(i As Long)
    Dim writerow As Long
    writerow = lastrow + 1
    
    Cells(writerow, "A").Value = "Error"
    Cells(writerow, "B").Value = i & "行目が空欄です"

End Sub

Function lastrow() As Long
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
End Function

sheet1にコードを記述する

A列を上から下まで処理をする中で「もし途中に空欄があったらエラーとしてログに書き出す」という機能を想定しました。

Option Explicit

Sub sample()
    Dim i As Long
    Dim lastrow As Long
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastrow
        If Cells(i, "A").Value = "" Then log.write_log (i)
    Next i

End Sub

実行

実行してみます。5行目、10行目、14行目にエラー（空欄）があります。

logシートに下記のようにエラーログが書き出せました。

まとめ

エクセルの一番下のセルから、上に向かって探す（end(xlup)）
最終行を求めることは頻繁に必要になるので関数化(function)すると便利