VBAで最終行を取得するには?|End(xlUp)、SpecialCells(xlLastCell)

Excelで最終行のデータを取得する方法が知りたい
選択したセルの範囲最終行を取得したい
取得できない場合はどうすればいいの?

Excelで表を作成していて何度も、最終行にデータを追加したり、削除したりすることってありませんか?データ数が多くなると最終行がどこか把握するのも大変ですよね。

そんな時こそ、VBAを使って自動化しましょう。

この記事ではVBAを使用して最終行を取得する方法について、基本的な方法から、空白セルが途中にあって取得できない場合、セルの範囲から最終行を取得する方法について解説していきます。

この記事は以下の内容で解説していきます。

  • Excelで最終行を取得したい
  • セル範囲から最終行を取得したい
  • 空白セルが途中にあり取得できない場合

読み終える頃にはVBAで最終行を取得できるようになりますので、ぜひ参考にしてください。

Excel VBAで最終行を取得する基本

キー操作で最終行を取得

最終行を取得する方法をVBAで作成するまえに、まずはキー操作だけで最終行まで飛ぶ方法について確認しましょう。

「Ctrl + ↓」キーでできます。

C4にカーソルを置き、「Ctrl + ↓」キーを押してみてください。

End01

このように最終行まで移動することがわかりますね。

End(xlDown)で最終行を取得

上記の操作をVBAで記述すると、以下のようになります。

Sub LastRowSample1()
 
    Cells(4, 3).End(xlDown).Select

    
    MsgBox "最終行は" & LastRow & "行目"
End Sub

実行結果:
End10

C4から開始しているのでCells(4, 3)と指定しています。

このようにEnd(xlDown).Selectを使うことで、「Ctrl + ↓」キーを押したときと同じように最終行を選択することができます。


【何から学べばいいかわからない…そんな悩みを解決します!】

完全無料ですぐわかる「プログラミング学習プラン診断」

・適性にあわせて学習プランを診断
・完全無料で気軽に診断できる
・学習の悩みもまるっと解決

さっそく診断してみる

空白セルが途中にあり取得できない場合は?

先ほどは「Ctrl + ↓」キーを押して最終行まで飛ぶ方法、End(xlDown).Selectを使う方法についてお伝えしました。しかし、これだけでは少々問題があります。

途中で空白セルがあった場合に、「Ctrl + ↓」キーを押しても最終行まで飛べないのです。つまり、途中の空白セルで止まってしまいます。

End02

D4にカーソルがある状態で、「Ctrl + ↓」キーを押してみましょう。空白セルの1つ手前、D6で止まってしまいます。

こんなとき、良い方法があります。

End(xlUp)を使って対策

それはいったん一番下のセルまで飛んで、次に上に上がるという方法です。一番下とは、Excelスプレッドシートの一番下という意味で、Excel2003形式(拡張子が.xls)では65536で、Excel2007以降(拡張子が.xlsx)では1048576です。

一番下のセルの行数はRows.Countで取得できます。上に上がるにはEnd(xlUp)を使います。

この操作をVBAで記述してみましょう。

Sub LastRowSample3()
 
    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Cells(Rows.Count, 1).Row  'Excelの最終行を取得
    LastRow = Cells(xlLastRow, 4).End(xlUp).Row   'D列の最終行を取得
    
    MsgBox "最終行は" & LastRow & "行目"
End Sub

実行結果:
End10

End03

使われたセル範囲から最終行を取得

これまでお伝えしてきた方法の他にも最終行を取得する方法があります。使われたセル範囲から取得する方法です。

いくつか方法がありますので、それぞれ説明していきます。

SpecialCells(xlLastCell)を使う方法

Excelシート上でA1を選択して「Ctrl + Shift + End」キーを押すと、以下のような状態になります。

End05

色がグレーに変わった範囲が、使われたセル範囲です。使われたセル範囲を取得し、同時に行と列の最終地点も取得できます。

先ほどのキー操作「Ctrl + Shift + End」キーは、VBAで記述するとSpecialCells(xlLastCell)になります。

サンプルコードで確認しましょう。

Sub LastRowSample5()
 
    Dim MaxRow As Long '最終セルの行番号
    Dim MaxColumn As Integer '最終セルの列番号
 
    MaxRow = Range("A1").SpecialCells(xlLastCell).Row
    MaxColumn = Range("A1").SpecialCells(xlLastCell).Column 

    MsgBox "最終行は" & MaxRow & "行目" & vbCrLf & _
            "最終列は" & MaxColumn & "列目"
End Sub

実行結果:
End06

UsedRangeを使う方法

WorkSheetオブジェクトのUsedRangeプロパティを使って使われたセル範囲を取得することもできます。UsedRangeのRowsプロパティの引数にUsedRange.Rows.Countで取得した総行数を指定することで、最終行を取得することができます。

サンプルコードで確認しましょう。

Sub LastRowSample6()
    Dim MaxRow As Long '最終セルの行番号
    Dim MaxColumn As Integer '最終セルの列番号
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    MaxRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
    MaxColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
    
    MsgBox "最終行は" & MaxRow & "行目" & vbCrLf & _
            "最終列は" & MaxColumn & "列目"
End Sub

実行結果:
End06

ただし、UsedRangeプロパティは罫線が引かれていると、罫線が引かれている行を最終行として取得するので注意しましょう。

End07

CurrentRegionを使う方法

RangeオブジェクトのCurrentRegionプロパティを使って使われたセル範囲を取得することもできます。CurrentRegionプロパティの引数にCurrentRegion.Countを指定することで、最終行を取得することができます。

サンプルコードで確認しましょう。

Sub LastRowSample7()
    Dim MaxRow As Long '最終セルの行番号
    Dim MaxColumn As Integer '最終セルの列番号
    
    MaxRow = Range("B4").CurrentRegion(Range("B4").CurrentRegion.Count).Row
    MaxColumn = Range("B4").CurrentRegion(Range("B4").CurrentRegion.Count).Column
    
    MsgBox "最終行は" & MaxRow & "行目" & vbCrLf & _
            "最終列は" & MaxColumn & "列目"
End Sub

実行結果:
End06

ただし、CurrentRegionを使う場合は空白行が途中であると上手くいきませんので注意しましょう。

End09


【なかなかエラーが解決できない…そんな悩みを解決します!】

登録無料で始められるプログラミングスクール「侍テラコヤ」

・回答率100%のQ&A掲示板でエラーを解決!
現役エンジニアとのオンライン相談で悩みを解決!
・50種類以上の教材で複数言語を習得!

侍テラコヤについて詳しく見る

最終行の下にデータを追加

最終行を取得する目的は、その下にデータを追加するか、最終行のデータを削除する場合が多いです。今回は最終行の下に行を追加してみましょう。

Rows.CountとEnd(xlUp)を使って最終行を取得し、その値に+1して位置を指定します。先ほどの表を使って確認しましょう。

Sub LastRowSample4()
 
    Dim xlLastRow As Long       'Excel自体の最終行
    Dim LastRow As Long         '最終行
 
    xlLastRow = Cells(Rows.Count, 1).Row  'Excelの最終行
    LastRow = Cells(xlLastRow, 2).End(xlUp).Row   'D列の最終行を取得
 
    Cells(LastRow + 1, 2).Value = "生徒5"
    Cells(LastRow + 1, 3).Value = 60
    Cells(LastRow + 1, 4).Value = 60
    Cells(LastRow + 1, 5).Value = 60
    Cells(LastRow + 1, 6).Value = 60
End Sub

実行結果:
End04

実行結果の写真では追加したデータにさらに罫線を付けています。罫線を付ける方法については、こちらのサイトで詳しく解説していますので、ぜひ参考にしてください。

初心者必見!VBAで罫線を引く方法をわかりやすく解説!
更新日:2023年3月21日

まとめ

本記事では、セルの最終行を取得する方法について、さまざまパターンを解説しました。この方法を使って、業務に応用できそうですね。
ぜひ、最終行の取得を使いこなしてみてくださいね!

プログラミング学習中、
誰かへ相談したいことはありませんか?

SAMURAI TERAKOYA

プログラミングはエラーの連続。作業を進めるなかで誰かに教えてほしい場面は多いですよね。

そんな悩みを解決できるのが、侍テラコヤです。登録無料で始められて、現役エンジニアとオンラインで相談できるほか、回答率100%のQA掲示版でプログラミングに関する悩みを解決します!

気になる方はぜひ詳細をご覧ください。

→侍テラコヤの詳細をみる

関西在住のITエンジニアです。普通の会社に勤務しながら、侍エンジニアのインストラクター、ライターとして活動しています。

あなたの目的に合わせた
SAMURAI ENGINEERの運営サービス

SAMURAI ENGINEER Pro

未経験でも挫折しないプログラミングスクール

詳細はこちら

SAMURAI TERAKOYA

日本最大級のサブスク型オンラインITスクール

詳細はこちら

SAMURAI ENGINEER Freelance

「一人で稼げる」スキルを身につける

詳細はこちら
Close