最終行を取得する方法7選 (UsedRange/End～)

Excelのデータ処理をマクロで自動化しようと思ったら、
必ずついて回るのが「最終行の自動取得」です。

データの処理がどんなものであれ、
まずはデータがどこまで入っているかを確認して、
処理を実行する範囲を決めないといけません。

Excelは非常に自由度が高いアプリケーションで、
入っているデータの形も様々です。

最終行の取得方法も、シートのレイアウトによって使い分けなければいけません。

初心者向けのものから応用の効く手法まで幅広く用意しましたので、
お好きなものをお持ち帰りください。

ワークシートの使用範囲の最後行

UsedRangeプロパティ

まずは最も基本的なコードから。

ワークシート.UsedRange

このコードで、「ワークシートで使われている範囲」を取得できます。

↑例えばこの表では、

Worksheets("データ").UsedRange

このコードで「B2:E8」のセル範囲をもつRangeオブジェクト
（ ≒ Range("B2:E8")と同じもの）を取得することができます。

さて、ここから「最終行番号」を取得しましょう。

セル範囲.Rows.Countで、セル範囲の行数を調べることができますので、　

Worksheets("データ").UsedRange.Rows.Count

これでUsedRangeの行数がわかります。

が、ここで注意が必要です。

このUsedRangeは「B2:E8」を取得していますので、
単にRows.Countで取った行数には、上部余白の1行目がカウントされません。

この余白の行数は、この表の第1行(B2:E8なら2行目)のひとつ上までですので、
セル範囲の第1行を取得するセル範囲.Rowを使って、

Worksheets("データ").UsedRange.Row - 1

これがUsedRangeの上部余白の行数となります。

この2つを足して、「最終行番号を取得するコード」はこちらが完成形です。

Dim 最終行 As Long
最終行 = Worksheets("データ").UsedRange.Rows.Count + Worksheets("データ").UsedRange.Row - 1

　
　
親シートの指定が2回出てきますし、
どうせこの後はこのシートへの処理が続くことが多いでしょうから、

Dim wsデータ As Worksheet
Set wsデータ = Worksheets("データ")
Dim 最終行 As Long
最終行 = wsデータ.UsedRange.Rows.Count + wsデータ.UsedRange.Row - 1

と親シートを変数に入れてしまうと読みやすくなって良いでしょう。
(またはシートオブジェクト名をつけるなど)

こうやって最終行を変数に取得しておけば、

Dim 最終行 As Long
最終行 = wsデータ.UsedRange.Rows.Count + wsデータ.UsedRange.Row - 1

' B列のデータ部分をクリアする
wsデータ.Range("B3:B" & 最終行).ClearContents

' データをループ処理
Dim R As Long
For R = 3 To 最終行
   wsデータ.Cells(R, 2) = ～～
Next

このように、データが何行入っているかを自動で取得して動くマクロが作れますね。

UsedRangeはプログラムの動きが分かりやすく、落とし穴があまりないので、
1つだけ覚えて帰るのであれば、こちらをおすすめします。

コードを書くときに気を付ける点としては、
VBAにしては珍しくActiveSheetが省略できません。

' ↓これはエラー
UsedRange.Rows.Count + UsedRange.Row - 1

' ↓このように明示しなければいけない
ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

　
といってもそもそもActiveSheetを頼ったコードは結構あぶないので、
UsedRangeだけでなく、RangeやCellsの親シートも省略しないようにしましょう。

wsデータ.UsedRange
wsデータ.Range("B3:B" & 最終行)
wsデータ.Cells(R, 2) 

ActiveSheetを処理するマクロは途中ユーザーのクリックひとつでバグりますので、
こう書くように癖をつけてください。

ちなみに最終列もほとんど同じコードで取得可能です。

Dim 最終列 As Long
最終列 = wsデータ.UsedRange.Columns.Count + wsデータ.UsedRange.Column - 1

UsedRangeプロパティの落とし穴

冒頭の通り「最終行の取得」にはいろいろな書き方があり、
どれがいいというわけではなく一長一短です。

UsedRangeさんはこんな表がちょっと苦手です↓

このとき、UsedRangeは「10」と、
書式が設定されている最終行を持ってきてしまいます。

「Used」なので、厳密に言えば「入力最終行」ではなく、
「使用中の最終行」なんですね。

当然この表の欄外に注釈のテキストなどがあっても、
UsedRangeはエリアを広く取りすぎてしまいます。

この方法で最終行を取得する場合は、シート全体のレイアウトにご注意を。

といっても、そこまで恐れる必要はありません。

こういった表を扱う場合は、

最終行 = wsデータ.UsedRange.Rows.Count + wsデータ.UsedRange.Row - 1
For R = 3 To 最終行
    If Cells(R, 2) <> "" Then ' ← B列に値がある行だけを処理
        ここにメインの処理を書く

と、ループ中のIf文で「データがあるか」を判定するだけで対応できます。

ちなみに「列全体を書式設定」していても、
UsedRangeはシートの最下部1048576を取得したりはしません。

全体に一括の書式設定をした場合は、それはUsedRangeと見做されない仕様です。

特定の列の入力最終行

Endプロパティ

続いて、「ある列でどこが最終行か」を調べるコードを紹介します。

この表で入力が完成していない8行目ではなく、
売上が入っている最終データ「7」を持ってくる場合に使います。

やり方としては、

と、Ctrl +↑↓←→の「区切りのセルまで飛ぶ」機能を利用します。

VBAでこの機能は「セル.End(方向指定)」で呼び出すことができます。

方向	記載するコード
下	End(xlDown)
上	End(xlUp)
左	End(xlToLeft)
右	End(xlToRight)

一番上のセルから下方向に「Ctrl + ↓」の方が自然に感じますが、
空白セルがあるとそこで止まってしまいますので、
すごく下のセルから上方向に飛ぶ手法を使います。

これを用いて、最終行を取得するコードがこちらです。

Dim E列の最終行 As Long
E列の最終行 = wsデータ.Range("E1000000").End(xlUp).Row

Endプロパティは飛んだ先の「セル」を取得しますので、
取得したセル.Rowで、その行番号を取得すれば最終行取得は完了です。

さて、このE1000000がちょっとカッコ悪いですね。

こういう「多分大丈夫そうなでっかい数字」を使うのは、
ダメプログラマあるあるでよく挙げられてる行為です。

Excel2025とかで1億行までOKになったらバグるかもしれませんしね。

ということで、カッコつけるとこんな感じになります。

Dim E列の最終行 As Long
E列の最終行 =wsデータ.Cells(wsデータ.Rows.Count, 5).End(xlUp).Row

「セル範囲.Rows.Count」はそのセル範囲の行数を取得しますが、
「ワークシート.Rows.Count」はシートのすべての行数をカウントします。

この場合、Cells(wsデータ.Rows.Count, 5)は"E1048576"を指します。

このように、「ある列の入力最終行」が欲しいときには、
UsedRangeプロパティではなくEndプロパティを利用しましょう。

せっかくなのでCtrl + ↓も一緒に

先ほど「一番上からCtrl + ↓では空白で止まってしまう」と注意しましたが、
この仕様を逆に利用したい場面もあります。

↑こんな表で、4月の表の最終行を取得する場合です。

1ヶ月でこれしか売れてないのはちょっと心配ですが、この時は、

Dim 上の表の最終行 As Long
上の表の最終行 = Range("B3").End(xlDown).Row

これで5月の表を無視して↑の表の最終行「8」を取得することができます。

もちろん上の表に空セルがあったらダメと言うことは忘れずに。

Endプロパティの落とし穴

さてこのEndプロパティさん、実は結構危ないやつです。

「表の下の余白部分になにかテキストがあるとそこで止まってしまう」
というのがすぐに思いつく罠ですが、それよりはるかに恐ろしい罠が…。

最も危険な罠は、「表示されている最終行しかとってこない」こと、
言い換えれば「オートフィルター中の表には使ってはいけない」ことです。

↑この表でEnd(xlUP)を使うと「6」が返ってきてしまいます。

オートフィルターとは関係なく全行を処理したいときに、
7～8行目が無視されてしまうバグになりますね。

もし使用する際はオートフィルターをクリアしてから実行しましょう。
詳しい解説は省きますが、

If wsデータ.AutoFilterMode = True Then ' フィルターが設置されていて
    If wsデータ.AutoFilter.FilterMode = True Then ' かつどこかの列で抽出がかかっているなら
        wsデータ.ShowAllData ' オートフィルターをクリアして全行を表示
    End If
End If

を直前に書いておけば、この落とし穴は半分は回避可能です。

もう半分、行自体を手で非表示にされている場合はどうしようもありません。

小ネタ：とても有名なEndさん

さてこのEndプロパティさん。
こんなに癖がある割にかなり有名で、現在のVBA界では不動のスタメンです。

何故？

「最終行 vba」で検索すると、UsedRangeよりはるかに多くの記事が出てきます。

そのため現世のVBAユーザーの多くがEndを採用し、
今日もどこかで誰かがオートフィルターの魔の手にかかっています。

悲しいね(´・ω・`)

私はVBA歴が浅いので、なぜこっちが主流なのかは分かりません。

原因を推測をすると「ActiveSheetを省略できるから」でしょうか？

最終行 = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
最終行 = Cells(Rows.Count, 1).End(xlUp).Row

シートを指定する方法を学ぶ前だと、Endの方が短くて教えやすいのかな？

ですがUsedRangeは書き方がとても素直です。

End(xlup)
UsedRange.Rows.Count

直訳すると「上端 vs 使用範囲の行の数」

初見の人でも「最終行」と感じれるのは圧倒的にUsedRangeなので、
初心者にとってもUsedRangeの方がいい気がするんですけどね。

加えてUsedRangeは知識としての価値も高いです。

Endプロパティは最終行以外だとめったに使わない手法ですが、
UsedRangeで使った「セル範囲の行数・第1行を求める」手法は、
これからVBAを学んでいく上で、肝となる重要なプロパティです。

「読みやすさ」「知識の価値」「安全性」すべてで勝るため、
私はUsedRangeの採用をおすすめします。

しかし、Endプロパティはとてもメジャーな書き方なので、
「Endで書かれた他人のコードを読む」という大事な役割があります。
覚えておきましょう。

ということで、まずは最終行取得の基本である、
「UsedRange」「End」の2プロパティを紹介しました。

この2つはこれから一生使います。
VBAを始めたばかりの方は、ここまでの内容をまずしっかりマスターしてください。

最終行を取得する自作関数を作る

さてこれらの最終行取得コードを実際に使うにあたり、
劇的にマクロの作成効率を上げる方法があります。

それは最終行を取得する自作関数を作ることです。

関数を作ると聞くと難しく感じるかもしれませんが、
この最終行の取得に関しては、作成がものすごく簡単です。
（というかこのページをコピペして持っていくなら作る必要もないですが）

苦手意識は一旦忘れて、こちらのコードを見てみてください↓

' UsedRangeを使った最終行取得関数
Function Getシート最終行(ws As Worksheet) As Long
    Getシート最終行 = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1
End Function

' 使用例
Dim LastR As Long
LastR = Getシート最終行(Worksheets("データ"))
For R = 3 To LastR
    ここに行ごとの処理を書く
Next

見てわかる通り、関数も中身はたった1行で、
しかも中身は上で紹介した基本のコードそのまんまです。

こんな簡単な関数ですが、使用例を見ればわかる通り、すごく便利です。

コード自体も短いですし、なによりこのコードが「データシートの最終行を取得している」ことがもはや文章のように読めるようになっていますね。

同じくEndプロパティを関数化したものがこちら。

' Endを使った最終行取得関数
Function Get指定列の最終行(ws As Worksheet, C As Long) As Long
    Get指定列の最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp).Row
End Function

' 使用例
Dim LastR_売上列 As Long
LastR_売上列 = Get指定列の最終行(Worksheets("データ"), 5)
Set 売上列のデータ部 = ws処理シート.Range("B3:B" & LastR_売上列)

こちらもものすごくわかりやすくなりますね。

読みやすさに加えて、コードを暗記しなくてよくなるのもかなり重要なメリットです。

この関数だけコピペしておけば、そのブック内ではもうUsedRangeやEndを書く必要がないということですからね。

関数は「複雑な処理のためのもの」と思っている方がいらっしゃいましたら、
それは誤解です。

むしろ今回のように、よく出てくる処理にただ名前を付けている関数の方が、
理解しやすく、作りやすく、使いやすい関数になります。

Function Sample1

みたいなサンプルコードで関数を学ぼうとするとわけわかんなくて煙が出ますが、
　

Function Getシート最終行

のように目的がはっきりしているコードで学ぶ関数は案外簡単です。

関数に苦手意識があった人も、これをきっかけに是非勉強してみて下さい。

指定したセル範囲(Rangeオブジェクト)の最終行

ここからいろんなパターンを紹介していきます。

まずは「指定したセル範囲の最終行」を取得しましょう。
Range("B2:E8")というセル範囲から、8を求めるシンプルなコードです。

そのコードがこちらです。

Function Getセル範囲の最終行(セル範囲 As Range) As Long
    Getセル範囲の最終行 = セル範囲.Rows.Count + セル範囲.Row - 1
End Function

なんてことはない、さっきのUsedRangeと同じ理屈のコードですね。
「セル範囲の行数 + セル範囲の第1行 - 1」で、最終行を求めています。

これで、Range("B2:E8")という範囲から、8を求める関数ができました。

なんでそんな単純なものを？
と思ったかもしれませんが、これが素晴らしく便利なのです。
　

作るマクロのレベルが上がると、Rangeオブジェクトを頻繁に扱うようになります。

「ExcelVBAの極意はRangeオブジェクトをいかに扱うか」
と言っても過言ではありません。

このとき、
「Rangeオブジェクトを渡せば、その情報を返してくれる関数」
があるととても便利なのです。

その証拠に、例えば最初に紹介したUsedRangeの最終行は、

シートの最終行 = Getセル範囲の最終行(wsデータ.UsedRange)

これで済むようになっています。

格段に楽に書けるようになりましたね。

この関数を作って持っておくとことは、
「この世のすべてのRangeオブジェクトにその最終行を返すプロパティを追加出来た」
ようなものなのです。

結構万能感ありません？

このように「Rangeオブジェクトを渡すと情報をくれる関数」は、作成しておくととても便利ですので覚えておきましょう。

表形式データの最終行

さてこの万能アイテムを手に入れたことで、
どんなRangeオブジェクトの最終行も持ってこれる状態になりました。

ということは「便利なRangeオブジェクト = 便利な最終行」と言えるわけです。

この便利なRangeオブジェクトの代表として、
「表形式データの範囲全体」を取得するプロパティを二つ紹介します。

CurrentRegion

あるセルを含む、表っぽい部分を自動取得してくれます。

この機能は「アクティブセル領域」と呼び、
「Ctrl + Shift + :」 にショートカットがセットされています。

普通にExcelで使えますので、試しにやってみてください。

今回の表の最終行を取得する際は、

' ベタ打ちならこれ
表の最終行 = Range("B2").CurrentRegion.Rows.Count + Range("B2").CurrentRegion.Row - 1

' さっきの関数を使うとこれ
表の最終行 = Getセル範囲の最終行(Range("B2").CurrentRegion)

こう書けます。
すっきりしていていいですね。

CurrentRegionによる最終行取得のメリットは、
シート内に複数の表があったり、注釈テキストが余白に書いてあっても、
そこそこ正確に「その表だけ」を見て最終行を取得できることです。

途中「すべて空の行」が挟まると、途切れてしまう弱点はありますが、
非常に強力なプロパティですので覚えておきましょう。

AutoFilter.Range

オートフィルターがかかっている表では、
「どこまでがデータか」の情報はAutoFilterオブジェクトが持ってくれています。

この表では、

Dim 表のセル範囲 As Range
Set 表のセル範囲 = wsデータ.AutoFilter.Range

とすることで、Range("B2:E8")を取得することができます。

オートフィルターが「シートに1個まで」という仕様なので、
ワークシートを指定するだけでこの範囲がもらえるのが便利ですね。

コード中にセルやセル範囲の情報を書かなくてよいということです。

先ほどと同様、最終行を取得する場合は、

' ベタ打ちならこれ
表の最終行 = wsデータ.AutoFilter.Range.Rows.Count + wsデータ.AutoFilter.Range.Row - 1

' さっきの関数使うとこれ
表の最終行 = Getセル範囲の最終行(wsデータ.AutoFilter.Range)

と、こちらもかなりスッキリしたコードで最終行を取得できます。

加えて「データの第1行」を取得する

wsデータ.AutoFilter.Range.Row + 1 ' ←1行目は「▼のついた見出し」なので+1

も一緒に使うことで、

Dim R As Long
Dim R1st As Long: R1st = wsデータ.AutoFilter.Range.Row + 1
Dim RLast As Long: RLast = Getセル範囲の最終行(wsデータ.AutoFilter.Range)
For R = R1st To RLast
    ここに行ごとの処理を書く
Next

と、ベタ打ちの数字やセルアドレスが一切ない完全に動的なコードが書けるようになるのがオートフィルターの強みでしょう。

弱点はオートフィルターの設定がおかしいと上手く動かないことですが、
それはVBA以前の問題ですのでマクロの前にシートを直しましょう┐(´∀｀)┌

CurrentRegion、AutoFilterの両プロパティとも、表を処理する際はとても便利なプロパティですので、是非覚えておきましょう。

セル範囲内の「入力」最終行

冒頭で紹介した一番基本となる「UsedRange」は、
「書式だけの行も検知してしまう」のが落とし穴でしたね。

この解決は↑のCurrentRegionを使うことで大体解決します。

CurrentRegionは値の入っているセルだけを検知しますので、
CurrentRegionの最終行は、必ず何らかのデータはあります。

ただ、CurrentRegionが何らかの事情でうまく動かない場合は、
もうがんばってやるしかなくなります。

そのコードがこちら

Function Getセル範囲の入力最終行(セル範囲 As Range) As Long
    With セル範囲
    
        ' セル範囲のおしりから行ごとにループ
        Dim R As Long
        For R = .Rows.Count To 1 Step -1
    
            ' 空でないセルが1個以上ある行を見つけ次第、行番号を返してExit
            If WorksheetFunction.CountIf(.Rows(R), "<>") > 0 Then
                Getセル範囲の入力最終行 = R + .Row - 1
                Exit Function
            End If
    
        Next
        
    End With
End Function

構造はコメントの通りです。

おしりから順にCOUNTIFで空でないセルをカウントし、
1個以上あった行が、「入力」最終行です。

ちなみにCOUNTIFでやると、「数式の結果が""」であるものも空セルと判定します。

数式もない、本当の空のセルだけを判定したい場合は、COUNTBRANK関数で書き換えてください。

Excelには便利なプロパティがたくさんありますので、
こういった愚直な力技はなるべく使わないようにしたいのですが、
シートのレイアウトなどでどうしようもなくなる時はもちろんあります。

その時は、あきらめてゴリゴリ力業コードを書いてください。

まとめ

今回は「最終行を取得するコード」

1. UsedRangeプロパティによる「シートの最終行」
2. Endプロパティによる「特定の列の最終行」
3. 関数を作ることで書きやすく&読みやすく
4. Rangeオブジェクトに情報を追加する「セル範囲の最終行」
5. CurrentRegionプロパティによる「表の最終行」
6. AutoFilter.Rangeプロパティによる「表の最終行」
7. COUNTIF関数をループする「入力最終行」

を紹介しました。

冒頭にも書いた通り、どれがいいというわけではなく一長一短です。

シートのレイアウトを見ながら、適材適所で使っていってください。

おまけ：関数づくりのコツ2選

最終行取得コードを関数化しておくと便利というのは上で述べた通りですが、
実際に使うときは、利便性を追求していきたいところです。

関数を「かゆいところに手が届く」ようにするためのテクニックを紹介します。

英語＋日本語で命名する

今回出てきた関数名は、分かりやすい様に

Function Getシート最終行
Function Get指定列の最終行
Function Getセル範囲の最終行
Function Getセル範囲の入力最終行

こう書いていました。

すごくわかりやすいのですが、書くのがちょっと面倒ですよね。

また、漢字は英語より見やすいことが多いのですが、
「行/列」だけは「R/C」の方が見やすいという方が多いと思います。

これを解決するには、関数名を以下のように名付けます。

Function GetLastR_シート
Function GetLastR_指定列
Function GetLastR_セル範囲
Function GetLastR_値あり

　
こうしておくことで、

• Get、Last、Rなどのシステムっぽい部分は英語が見やすい
• シート・セル範囲など実際の動きに関する部分は日本語が見やすい

と、両言語のいいとこどりができて読みやすくなります。

そして一番の目的は入力を楽にすることで、
Ctrl + Space から「入力候補の表示」を使うと、

ここから選べるようになります。

これで日本語入力をONにしなくても日本語を入力できますので、

「日本語読みやすいけど書くのは面倒だな～」

と思っていた方は、是非活用してみてください。

詳しくはこちらをどうぞ

www.limecode.jp

ひとつの関数に複数の動きをしてもらう

上記のように関数を増やしていくのも便利なのですが、
関数が増えすぎると選ぶのが面倒になってきます。

そんな時は、ひとつの関数にたくさんの役割を担ってもらいましょう。

関数に渡す「指定列」を省略できるようにし、

GetLastR(ws処理シート) ' ← これだとUsedRangeでシートの最終行
GetLastR(ws処理シート, 2) ' ← これだとEnd(xlUp)でB列の最終行

こんな風に、列を指定したかどうかを判定し、UsedRangeとEndをハイブリットで動かせる関数を作ることができます。

そのコードがこちら↓

' 最終行取得の汎用関数
Function GetLastR(ws As Worksheet, Optional C As Long = -1) As Long 
    With ws                          ' ↑Optionalをつけた変数は省略可になる。省略時は-1
    
        ' 列番号を省略時はシートの最終行
        If C = -1 Then
            GetLastR = .UsedRange.Rows.Count + .UsedRange.Row - 1
    
        ' 列番号を指定時は列の最終行
        Else
            GetLastR = .Cells(.Rows.Count, C).End(xlUp).Row
        End If
    
    End With
End Function

　
引数を省略可能にするOptionalキーワードを使用することで、
ハイブリッドな関数を作ることができます。

いろいろ研究してみてください。

私の作成したハイブリッド関数がこちらになります↓
よろしければどうぞ(´∀｀)
www.limecode.jp