エクセルVBAで最終行まで繰り返し処理！初心者も大丈夫！
初心者エクセルVBAなら最終行まで自動処理できるの？！繰り返し処理がうまくいかなくて…
Dr.オフィス大丈夫、基本の型を押さえれば簡単だよ！
最終行を取得して自動処理するマクロは、日々、リストの数が変わる実務において必須のスキルです。
今回は、マクロ初心者の最大の難関である「最終行がその都度変わるリストの処理」について解説します。
エクセルVBAで最終行を取得するポイント
- 最終行はセルの下から上に向かって探す
End(xlUp)が鉄板 - 繰り返し処理には
Do Loop文よりFor Next文が簡単で安全 - 最終行の1つ下は
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1と記述する
最終行までの繰り返し処理は、一見難しそうですが、基本の型さえ押さえれば簡単です。
この記事を読みながら、小さなリストから試してみましょう。
また、最終行までの繰り返しと併せて活用したい「マクロの基礎」は、こちらからご覧ください。
エクセルマクロの基礎をゼロから学ぶ！有効化やボタン設置！
※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を用いて解説をしています。
＼ Officeドクター読者限定・無料Q&A ／
この記事の内容でわからないことや、今すぐ解決したいOfficeのお悩みはありませんか？
Officeドクターの中の人が、公式LINEで直接ご質問にお答えします！
下のボタンをタップして、表示された入力欄からそのまま送信してくださいね。
目次
エクセルVBAで最終行を取得して繰り返し処理する基本の流れ
エクセルVBAで自動化の仕組みを作る際、最初に出会う壁は「最終行までの繰り返し処理」です。
Dr.オフィス繰り返し処理のことを「ループ」とも言うよ！
名簿の作成や売り上げの集計、データの転記など、マクロで繰り返し処理したいもののほとんどが、上から順番に同じ作業をしています。
実務のデータは行数が固定されておらず、日々増減します。
そのため、エクセルVBAで実務データの自動化を行う場合は、マクロが動いた瞬間に、データが何行目まであるかを自動判定させる作業が必要です。
エクセルVBAで最終行を取得するのは、そのためです。
最終行まで繰り返し処理するコードの基本構成は以下のとおりです。
- 変数の宣言
最終行の行番号と、繰り返し回数を格納する変数を準備 - 最終行の取得
データの1番下の行番号を取得し、1.の変数に格納 - 繰り返し範囲の指定
何行目から、最終行まで処理するか範囲を指定 - 実行したい処理の記述
実際に行いたい作業をループの中に書く - ループの終了と次の行へ
1行分の処理が終わったら自動的に次の行へ移動 - 最終行まで3～5を繰り返す
この構成でVBAコードを書くことで、データが増減してもコードを修正する必要がなくなります。
一度、この基本構成を覚えれば他の業務に応用が可能ですので、ぜひ覚えましょう。
VBAで最終行を取得する鉄板コードを解説
VBAで最終行を取得する方法はいくつかありますが、実務で多く使われる鉄板のコードがあります。
ここでは、具体的なコードの仕組みと、ほかの方法ではなくこの書き方が必要な理由を解説します。
解説用のデータとして以下の県庁所在地一覧を使います。
どのデータでも仕組みは同じですので、お手元のデータに置き換えてご参照ください。
下から探すEnd(xlUp)が最終行取得の鉄板
最終行取得のために使われるのが、以下の鉄板コードです。
Dim 変数 As Long
変数= Cells(Rows.Count, 1).End(xlUp).Rowそれぞれのパーツの役割は以下のとおりです。
- Dim 変数 As Long
-
最終行を格納するために「変数を使う」宣言をしています。
As Longで変数に入れるデータの種類を決めています。Longは21億まで数えられる「長整数型」です。他に数字を指定するものとして
Integerがありますが、こちらは約3万までしか数えられません。最終行を取得する場合には
Longを使うことをお勧めします。 - 変数
-
変数に、取得した行番号を格納します。
変数はどんな名称でも構いません。
VBAの世界では「最終行を入れる箱」として
lastRowがよく使われます。 - Cells(Rows.Count, 1)
-
Cells(行番号,列番号)で、最終行を取得するために、どこを基準として調べ始めるかを指定しています。Rows.Countでエクセルシートの一番下の行を指定しています。
Dr.オフィスエクセルシートの一番下の行は104万8,576行だよ！
1はA列を基準にしていることを表しています。もしもB列を基準とするなら
2、C列なら3に変更しましょう。 - End(xlUp).Row
-
End(xlUp)で、エクセルシートの一番下の行から上に向かってジャンプして、データがある場所までワープするよう命令しています。Dr.オフィスエクセルのショートカットキー
Ctrl＋↑と同じ動きだよ！.Rowで、ワープして着地したセルの行番号を取り出します。
この動きを実際のリストに図解すると以下のとおりです。
エクセルシートの一番下の行から上に向かってジャンプし、A列「団体コード」の最初にぶつかったデータ入りのセル「A48」が最終行として特定され、変数には48が格納されることとなります。
シートの底から上に向かってジャンプすることが、データ量に左右されない鉄板の書き方です。
xlDownは空白セルで止まるので最終行取得には向かない
初心者End(xlUp)で下から上に向かって最終行を探しているけど、上から下に向かって探すほうが速くない？
Dr.オフィス上から下に向かって探すのはお勧めしないよ！
End(xlUp)とは反対に、上から下に向かって探す方法としてEnd(xlDown)があります。
これはエクセルでCtrl＋↓を押すのと同じ動きです。
以下のコードを実行した場合を考えてみます。
Dim 変数 As Long
変数= Cells(1, 1).End(xlDown).RowこのコードはCells(1, 1)、すなわちA1セルから下に向かって最終行を検索しています。
一見、End(xlUp)よりもこちらのほうが検索方法として自然に見えますが、実務データは必ずしもすべてのデータがきれいに詰まっているとは限りません。
End(xlDown)の場合、リストの途中で空白セルがあると、そこが最終行だと判断して止まってしまいます。
上記のリストの場合、A列「団体コード」の最初の空白セル「A8」の1つ上が最終行として特定され、変数には7が格納されることとなります。
xlDownが使えるのはデータが1行目から最後まで1つも欠けずに詰まっている場合だけであり、実務で使うには現実的ではありません。
データが不完全でも確実に最終行を取得できるよう、「下から上へ探す」を必ず覚えておきましょう。
エクセルVBAで最終行まで繰り返すサンプルコードを3つ紹介
最終行を取得する方法と仕組みを理解したら、次は、実務で活用してみましょう。
ここでは、現場でそのままコピーして使える「よく使う3つのコード」を厳選してご紹介します。
実務に応じてコードは適宜変更していただいて構いませんが、実行する前に必ずバックアップを取り、少量のデータから試すことをお勧めします。
2行目から最終行までFor Nextで繰り返すコード
まずは、最も基本となるFor Nextを使った繰り返し処理から見ていきましょう。
県庁所在地一覧の右端に、順番に番号を振っていく「連続採番」のコードをご紹介します。
VBAエディターを開くところから一緒に作業してみましょう。
STEP
標準モジュールを挿入する
- 「挿入」タブをクリック
- 「標準モジュール」を押す
STEP
プロシージャを挿入
- 「挿入」タブを押す
- 「プロシージャ」をクリック
STEP
マクロ名を入力
- マクロ名を入力する
- 「OK」ボタンを押す
STEP
コードをコピー＆ペースト
Public Sub 連続採番()～End Subの間に以下のコードをコピーして貼り付けましょう。
Dim lastRow As Long
Dim i As Long
' ① A列の最終行を取得して変数に入れる
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
' ② 2行目（見出しの次）から最終行まで繰り返す
For i = 2 To lastRow
' G列（7列目）に、上から順番に番号を入力する
Cells(i, 7).Value = i - 1
Next iコードの説明は後ほど行います。
STEP
マクロを実行する
- 「▶」ボタンを押す
- エクセルに戻り、G列に採番されていることを確認
STEP
最終行まで連続採番された
リストの一番下まで移動し、番号が振られていることを確認しましょう。
このコードのポイントは次のとおりです。
For i = 2 To lastRow～Next i-
2行目から最終行まで処理を繰り返しています。
特に指定がない場合、iは1ずつ増えていきます。
実務で使用するデータは1行目に見出しがあることがほとんどであるため、繰り返し処理は
For i = 2から開始するのが一般的です。もしも1行目にタイトル、2行目に見出しがあり、実際のデータが3行目から始まる場合は、
For i = 3に変更してください。 Cells(i, 7).Value = i - 1-
Cells(i, 7)のように行番号にiを使うことで、現在処理している行の左から7番目の列であるG列を操作できます。G列以外を操作したい場合は
7を他の数字にしてください。また、
.Value = i - 1で行番号から1をマイナスした数字が採番されセルに入力されます。1をマイナスする理由は、1行目に見出しがある分を差し引いているからです。
1行目にタイトル、2行目に見出しがある場合は、
.Value = i - 2としましょう。
For Nextはコードが分かりやすいので、まずはこの形をベースにして、実務に応じた処理を書き込んでみてください。
なお、条件を指定して行を削除するVBAは、上から下に向かって繰り返し処理すると行ズレによる処理漏れが起こるので注意が必要です。
行を削除する際は、For i = lastRow To 2 Step -1と記述して、一番下から上に向かって順番に処理を行うのが鉄則ですので、必ず覚えておきましょう。
最終行までRangeで範囲指定して一気にコピーするコード
最終行まで1行ずつ順番に処理する方法を使わず、範囲を一気に指定して一度に処理するコードをご紹介します。
この処理は、大量にあるデータを別の場所にコピーする場合に便利です。
例えば、C列の最終行までの内容をH列に一度にコピーするサンプルコードは以下のとおりです。
Public Sub 一気にコピー()
Dim lastRow As Long
' ① A列の最終行を取得して変数に入れる
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
' ② C2からC列最終行までの範囲を、H2を起点に一気にコピー
Range("C2:C" & lastRow).Copy Destination:=Range("H2")
End Subマクロを実行して、最終行まで一度にコピーされていることを確認しましょう。
このコードのポイントはRangeと最終行の数を格納した変数lastRowを組み合わせていることです。
Range("C2:C" & lastRow)のように&でつなぐ書き方が重要なので覚えておきましょう。
例えばlastRowに格納された最終行が48の場合、Range("C2:C" & lastRow)はRange("C2:C48")に自動で書き変わります。
また、今回は一気にコピーする例をご紹介しましたが、コピーだけでなく同じ数式を一括入力することも可能です。
例えばRange("I2:I" & lastRow).Formula = "=G2*1.1"とすれば、G列の数字に1.1を掛ける数式を一度に入力できます。
最終行を取得したのちに1行ずつ条件を判定したい場合はFor Nextを使う、一度に同じ操作をしたい場合はRangeの範囲指定を使うと覚えておきましょう。
最終行の「1つ下」にインプットボックスを使ってデータを追加するコード
最終行を取得するコードは、繰り返し処理以外にも使える場面があります。
実務では、社員名簿や顧客リストに、新しいデータを1件ずつ蓄積していく場面がよくあります。
この場合、取得した最終行の行番号に1をプラスして、「一番下のさらに1つ下の空白セル」を指定するのがポイントです。
以下のコードは、マクロを実行するたびにインプットボックスを表示して「氏名・郵便番号・住所」を入力し、最終行のA～C列に自動で追加していくマクロです。
Public Sub 名簿に追加()
Dim nextRow As Long
Dim inputName As String, inputZip As String, inputAddress As String
'インプットボックスで1人分の氏名を取得
inputName = InputBox("「氏名」を入力してください")
'もし氏名が未入力やキャンセルされたらマクロを終了する
If inputName = "" Then Exit Sub
inputZip = InputBox("「郵便番号」を入力してください")
inputAddress = InputBox("「住所」を入力してください")
'A列の最終行に「+1」をして、次の空白行を取得
nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
'取得した新しい行のA～C列に、入力したデータを転記する
Cells(nextRow, 1).Value = inputName 'A列：氏名
Cells(nextRow, 2).Value = inputZip 'B列：郵便番号
Cells(nextRow, 3).Value = inputAddress 'C列：住所
MsgBox "名簿への追加が完了しました！"
End Subマクロを実行すると次のようになります。
- マクロを実行し、表示されるインプットボックスに氏名などを入力
- 最終行の1つ下に①で入力したデータが追加されることを確認
Dr.オフィス実務ではシート内にマクロボタンを設置して運用することが多いよ！
コードのポイントは、マクロを実行するたびにCells(Rows.Count, 1).End(xlUp).Row + 1を計算し、最新の最終行プラス1を取得することです。
これにより、データが上書きされることなく蓄積されていきます。
リストの内容に応じて、項目数を追加したり、項目名を変更したりしてご活用ください。
最終行までの繰り返し処理でよく使われるコードと注意点を解説
エクセルVBAで最終行までの繰り返し処理をする際、コードがいくつかあり「結局どれを使えばいいの？」と迷ってしまう方もいるのではないでしょうか。
結論からお伝えすると、For Nextが最も使いやすくて安全です。
それぞれのコードの具体的な書き方と、なぜFor Nextが実務で選ばれるのか、同じ処理を行うコードを比較しながら詳しく解説します。
作例として、D列に「発送完了」と入力する処理を最終行まで繰り返すコードを3種類ご紹介します。
For Nextが圧倒的に簡単で安全
まずは最も安全なFor Nextを紹介します。
For Nextの一番の特徴は、「2行目から始まり、最終行で終わる」といったように、処理の始まりと終わりがあらかじめ明確に決まっていることです。
Public Sub 発送完了チェック①()
Dim i As Long
Dim lastRow As Long
'A列の最終行を取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'2行目から最終行まで確実に繰り返す
For i = 2 To lastRow
'特定の条件でループを強制終了したい場合は Exit For で抜ける
If Cells(i, 1).Value = "終了" Then
Exit For
End If
'ここにメインの処理を書く
Cells(i, 4).Value = "発送完了"
Next i
End Subこのように、「どこまで繰り返すか」というゴールが決まっているので、意図しない場所で処理が中断するトラブルが起きにくいのが最大のメリットです。
また、特定の条件の場合はExit Forで繰り返し処理を強制終了することができるので、実務で扱いやすいと言えます。
Do Whileは無限ループの危険性があるので初心者にはお勧めしない
次に、Do Whileをご紹介します。
Do WhileなどのDo Loop文は、エクセルVBAの解説本でよく紹介されていますが、最終行までの繰り返し処理に使うのは要注意です。
Public Sub 発送完了チェック②()
Dim i As Long
i = 2
'A列のセルが空白になるまで繰り返す
Do While Cells(i, 1).Value <> ""
'ここに処理を書く
Cells(i, 4).Value = "発送完了"
'行番号を自分で1つ増やす（これを忘れると無限ループになる）
i = i + 1
Loop
End Subこのコードは一見シンプルで処理内容が分かりやすいメリットもありますが、注意点もあります。
Loopの前にi = i + 1と自分で行番号を1つ増やす必要があり、これを書き忘れると、永遠にi＝2行目を処理し続ける無限ループ状態に陥ります。
また、処理途中のA列に空白セルがあると、そこがデータの最終行だと勘違いし、処理が中断してしまいます。
そのため、最終行までの繰り返し処理には極力使わないのが無難です。
範囲を指定して高速処理するFor Eachもある
For Nextをマスターした後の応用編として知っておきたいのがFor Eachです。
For Eachは、行番号を1、2、3……と数えていくのではなく、あらかじめ指定したセル範囲の中にあるすべてのセルに対して一度に処理を行うコードです。
Public Sub 発送完了チェック③()
Dim lastRow As Long
Dim targetCell As Range
'A列の最終行を取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'2行目から最終行までのA列の範囲を対象にする
For Each targetCell In Range("A2:A" & lastRow)
'targetCellに対して直接処理を書く
targetCell.Offset(0, 3).Value = "発送完了"
Next targetCell
End SublastRow = Cells(Rows.Count, 1).End(xlUp).Rowで最終行を取得するのはFor Nextのときと変わりません。
For Eachでは、変数iを使って行番号を指定する代わりに、変数targetCellでセルそのものを指定しています。
そのため、変数targetCellに対して直接操作できるのが特徴です。
なお、.Offset(0, 3)で、A列の3つ隣であるD列を指定しています。
For Eachは指定したセルに対して一度に処理を行うため、非常に広範囲のセルを処理する場合は、For Nextよりも処理速度が速くなります。
まずは基本となるFor Nextで最終行まで繰り返し処理する感覚をつかんでから、応用編としてチャレンジしてみましょう。
最終行だけでなく最終列もエクセルVBAで取得できる
エクセルVBAでは、最終行だけでなく、右方向の「最終列」を取得して繰り返し処理を行えます。
例えば、1日ごとの売上など、横方向にデータが増えていく表を処理する際に役立ちます。
最終行を取得するときはEnd(xlUp).Rowを使って一番下から上に向かって探しましたが、同様に、最終列を取得するときは一番右から左に向かって探しましょう。
以下は、B列～最終列まで1行目のセルに色を塗る繰り返し処理するコードです。
Sub 最終列取得()
Dim j As Long
Dim lastCol As Long
'1行目の最終列（一番右の列番号）を取得
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
'2列目（B列）から最終列まで繰り返し処理
For j = 2 To lastCol
'1行目の各列に背景色をつける
Cells(1, j).Interior.Color = RGB(255, 255, 0)
Next j
End Subコードの構成は、最終行を取得して繰り返し処理する際の基本構成と同じです。
Columns.Countはシートの一番右の列であり、そこからEnd(xlToLeft)で左に向かってデータが入っているセルを探し、.Columnで列番号を取得しています。
最終行と最終列の取得を組み合わせることで、どんなに大きな表でも自動で処理できるようになります。
まずは、ご自身が業務で使っているデータに繰り返し処理がないか確認してみることから始めてみましょう。
エクセルVBAの最終行取得に関するQ＆A
エクセルVBAの最終行取得とForループで日々の単純作業を自動化しよう！
今回は、エクセルVBAで最終行を取得して繰り返し処理を自動化する方法を解説しました。
実務ではデータ数が毎回変わることはよくあるので、最終行取得と繰り返し処理の組み合わせは、マクロを使う上での必須スキルと言えます。
まずは最終行を取得して繰り返し処理するコードの基本構成をしっかりと覚えましょう。
それでは記事のポイントをおさらいしましょう。
おさらい
- 最終行はセルの下から上に向かって探す
End(xlUp)が鉄板 - 繰り返し処理には
Do Loop文よりFor Next文が簡単で安全 - 最終行の1つ下は
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1と記述する
最終行の取得と繰り返し処理をマスターすれば、日々の面倒なデータ整理を全自動化し、業務スピードが驚くほど上がります。
ぜひ、この記事をブックマークして、いつでも見返せるようにしてください。
また、最終行までの繰り返し処理がさらに活用できる「マクロの基礎」は、こちらからご覧ください。
エクセルマクロの基礎をゼロから学ぶ！有効化やボタン設置！