和風スパゲティのレシピ

日本語でコーディングするExcelVBA

最終行を取得する方法7選 - 基本構文とサンプルコード

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

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


Excelは非常に自由度が高いツールですので、
入っているデータの形も様々です。

最終行の取得方法もワークシートのレイアウトによって使い分ける必要があります。


初心者向けのものから、複雑な処理に耐えうるものまで幅広くご用意いたしましたので、お好きなものをお持ち帰りください。

ワークシートで使われている最後の行

UsedRangeプロパティ

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

ワークシート.UsedRange

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


UsedRangeで取得する表

↑例えばこの表では、

ActiveSheet.UsedRange ' =Range("B2:E8")

を取得することができます。


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

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

ActiveSheet.UsedRange.Rows.Count

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


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

↑で述べた通り、このUsedRangeはRange("B2:E8")を取得していますので、
単にRows.Countを取ると、「7」が返ってきてしまいます。

欲しいのは行番号である「8」ですので、
UsedRangeに含まれていない、1行目の「余白」もカウントする必要があります。


それを踏まえて、「最終行番号を取得するコード」はこちらが完成形です。

Dim シートの最終行 As Long
シートの最終行 = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

 
セル範囲.Rowで、その範囲の第1行番号を取得できます。
あとは↓の図の通り、「行数」と「第1行数」を足して、
被っている1行を引いて完成です。

UsedRangeの計算



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

' B列のデータ部分を取得する
Range("B3:B" & シートの最終行)

' データをループ処理
Dim R As Long
For R = 3 To シートの最終行
   Cells(R, 2) = ~~
Next

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


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


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

Dim シートの最終列 As Long
シートの最終列 = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1

 

UsedRangeプロパティの落とし穴

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

UsedRangeの短所はこちらの表で発生します↓

UsedRangeの落とし穴

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

「入力最終行」ではなく、「使用中の最終行」ですからね。


他には、この表の↓や→に、別のデータや注釈のテキストなどがあっても、
UsedRangeはエリアを広く取りすぎてしまいます。


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


といっても、そこまで恐れなくていい罠です。

もしこの罠が発動してしまったら、

For R = 3 To シートの最終行
    If Cells(R, 2) <> "" Then
        ここから処理を開始

などのように、ループの中で「データがあるか」を判定するだけで対応できます。


また、「行全体に書式を設定」していても、最後の行である1048576になってしまったりはしません。全体に一括の書式設定をした場合は、それはUsedRangeと見做されない仕様です。

ある列の入力最終行

Endプロパティ

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

Endプロパティで取得する表

この表で、売上が入っているデータだけを処理するために「7」を持ってくる場合に使います。


やり方としては、

Endモードの例

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


この機能はVBAでは「セル.End(方向)」で呼び出すことができます
方向の指定には、以下の定数を使用します。

方向 引数
xlDown
xlUp
xlToLeft
xlToRight


見出しのセルから「Ctrl + ↓」の方が自然に感じますが、
空白セルがあるとそこで止まってしまいますので、
すごく下の方から上へ向かって「Ctrl + ↑」で取得します。


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

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

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


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

「2003年版のExcelだと行数が足りずにエラーで止まる」という実害もありますし、
こういう「絶対使わないであろう大きい数」を使うのは、万が一使われた際に予期せぬエラーが発生するので、ダメプログラマあるあるでよく挙げられてる行為です。


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

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

セル範囲.Rows.Countはその範囲の行数を取得しますが、
省略した場合のRows.Countは、ActiveSheetの行数をカウントします。

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


これで、ActiveSheetがExcel何年版かによって違う行数を取得してくれますし、
1048576という数値を覚えていなくても、正確にMAX数でコーディングできます。

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

先ほどの「Ctrl + ↓」で、空白で止まってしまう仕様を、
逆に利用したい場面もあります。


連続データの取得

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

1ヶ月でこの売上というのはちょっと心配になりますが、この時は、

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

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

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

Endプロパティの落とし穴

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

予期せぬデータや空白に引っ掛かって途中で止まるというのが、
すぐに思いつく罠ですが、それよりもっと恐ろしいものが。


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


オートフィルター中の表

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

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


この落とし穴があり、書き方もちょっとトリッキーなので、
最終行を取得するメインコードにはUsedRangeを採用し、
「ある列の最終行を取ってきたい」ときに限定した使用をおすすめします。



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

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

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

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

さてこのEndプロパティさん。
こんなに癖がある割に、この世界では不動のスタメンです。

何故?


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

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

悲しいね(´・ω・`)


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


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

End(xlup)
UsedRange.Rows.Count

下は初めて見ても、最終行っぽいですよね。


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

Endは最終行以外だとめったに使いませんが、
UsedRangeで使った「.Rows」「.Count」「.Row」あたりは、
これからVBAを学んでいく上で、肝となる重要なプロパティです。


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


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

ここから中級編

まずは初級者向けに「UsedRange」「End」の2プロパティを紹介しました。

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


ということで、ここからは中級編に入ります。


基本の2プロパティ以外の、便利なプロパティをどんどん紹介していきたいところですが、その前に。


今紹介したこの2プロパティを使いこなす上で、
関数(プロシージャ)作成のメリットにも触れておきます。

汎用関数にする

今回のUsedRangeによる最終行取得のコードを、
いつでも使える汎用関数にしたのがこちらです↓

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

' 使用例:処理するシートの全データをループ処理
For R = 3 To Getシートの最終行(ws処理シート)

関数も中身はたった1行で、
上で紹介した基本のコードを直訳しているだけですが、
こんな関数でも非常にコードが読みやすくなります。


同じくEndを関数化したのがこちら。

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

' 使用例:B列のデータエリアを取得
Set 処理範囲 = ws処理シート.Range("B3:B" & Get指定の列の最終行(ws処理シート, 2))

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


コードが短くわかりやすくなるメリットに加え、

  1. プロパティの並びを覚えておかなくてもいい
  2. 変数を用意しなくてもいい

というのもなかなかいいポイントです。


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


今回のように、たった1行をただ読みやすくしただけの関数でも、
コードを読みやすく、書きやすくしてくる効果は十分にあります。

作った関数は他のマクロでも使いまわせますから、
どんどん作っていきましょう。

指定のセル範囲の最終行を取得する

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


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


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

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

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

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


なんでそんな単純なものを?
と思ったかもしれませんので使いどころを。


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

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


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


その証拠に、今回1番最初に紹介したUsedRangeの最終行を、
今回の関数を使って求めてみましょう。
 

Getセル範囲の最終行(ActiveSheet.UsedRange)

これで済むということです。

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


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

ようなものなのです。

結構万能感ありません?


「Rangeを渡すと情報をくれる関数」は、
これ以外でも便利な場面が多いです。

覚えておきましょう。

表形式データの最終行を取得する

さて、↑の☆アイテムを取ったあなたは、どんなRangeオブジェクトの最終行も持ってこれる無敵状態です。

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


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

CurrentRegion

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

CurrentRegionのサンプル

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

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


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

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

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

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


適材適所の観点で使いどころをお伝えしますと、ひとまず

  • UsedRangeの弱点「下方ゴミデータ・空書式」
  • End(xlUp)の弱点「フィルターなどの非表示」

これをどちらもクリアします。


途中「すべて空の行」が挟まると、途切れてしまう弱点がありますが、
「ちゃんとした形の表データ」を取得する場合は、
非常に強力なプロパティですので覚えておきましょう。

AutoFilter.Range

オートフィルターがかかっている表では、
「どこまでデータか」はオートフィルターが持ってくれています。


オートフィルターの表サンプル

この表では、

ActiveSheet.AutoFilter.Range ' = Range("B2:E8")

を取得してくれます。


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


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

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

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

こちらもかなりスッキリ書けます。


起点のセルすら指定しなくてもよい、
ワークシートで一意に定まるという「コードの頑丈さ」が売りです。

第1行を取得する

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

も併せて使えば、「どこからどこまで」をどちらも動的にできますね。


弱点は「すべて空の行が入ると途切れる」ことですが、
その場合はオートフィルターが途切れていますので、
そもそもVBA以前の問題で、ワークシートがおかしいです。


逆に言うと、VBAでバグが起きる前に、ワークシート上で異変に気付けるので、
これもちょっとしたメリットですね。


もちろんシートが壊されるとうまく動かないということですが、
そんなのこれに限った話ではないです┐(´∀`)┌



両プロパティとも、最終行取得に限らず、表を処理する際はとても便利なプロパティです。
覚えておきましょう。

入力されている最終行を取得する

冒頭で紹介した一番基本となる「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関数で書き換えてください。


このコードをもってようやく、Endプロパティでは果たせなかった「オートフィルターがかかっているデータの、ある特定の列の最終行取得」が果たせます。

まとめ

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

  1. UsedRangeプロパティ
  2. Endプロパティ
  3. 関数化のメリット
  4. 任意のRangeオブジェクトの最終行
  5. CurrentRegionプロパティ
  6. AutoFilter.Rangeプロパティ
  7. COUNTIFによる「入力」最終行

を紹介しました。


冒頭にも書いた通り、どれがいいというわけではなく一長一短がありますので、
使い分けていけるように頑張ってください。

おまけ:使い分けを楽にするしくみ

使い分けを考えていく上で、面白い関数化の方法があります。

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

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

こんな風に、基本はUsedRangeで、列を指定したときだけEndになる関数にすることができます。


そのコードがこちら↓

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

こんな風にハイブリッドにするのも面白いですね。

いろんなプロパティを勉強するのももちろん必要ですが、
既に知っているスキルをいかにうまく使うかを考えるのもとても大事です。

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

おまけ:関数名について

今回出てきた関数名は、

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

こう書いていました。


「VBAなんか外人どころか自分しか見ないんだから、変数と関数は日本語にしろ!」

という本ブログのテーマに沿って書いています。
読みやすいでしょ?


ただ、残念ながらVBAの主たるコードの内、
どうしても「日本語 << 英語」になる名詞があります。


ズバリ

Cells(R, C)
Cells(,)

これです。


日本語の「行」「列」ってちょっと似てるから、
行・列関連の変数名にするにはあまり向いてません。


R,Cの方が見やすいですし、
行列では取り違いを防げません。
 

Cells(売上表の列, 売上表の行)
Cells(C売上表, R売上表)

↑すぐに「やばい!」と思うのはやっぱり下です。


ということで、解説はわかりやすいようALL日本語でしたが、
関数をコピペして持っていく際は、

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

あたりに変えると良いです。


RLast自動メンバー

Ctrl + Space でこんな風に選べるようになるので、
見やすくなるついでに、日本語入力をONにしなくてよくなります。



なお、

GetLastR_WorkSheet
GetLastR_Column
GetLastR_Range
GetLastR_NotBrank

にしたい方は、
「その関数を外人さんが読むことがあるか」を、
もう一度自問自答してください。


それでもこちらを使うという方は、どうぞご勝手に(ノ#`Д´)ノ⌒┻━┻