＼Excelショートカット集もらえる／
【Excel VBA】終端セルを取得する方法をマスター！(End,Offsetを組み合わせよう)
本記事は、Excel VBAにおいて必ず覚える必要があるEndプロパティを使った「終端セルの取得」について説明します。終端セルを取得する方法だけではなく、終端セルを取得するコードを書くときの考え方や、終端セルの1つ下のセルを選択する方法についても解説します。
こんな方におすすめ
- Excelで作成したリストの最終行を取得したい
- 最終行の１つ下の行にデータを追加したい
目次
なんで最終行を選択する必要があるの？
皆さんは実務の場面において、次のようなデータの一番下に、新しいデータを追加したいと思ったことはありませんか？
この時にVBAを使わないで実行しようとすると、おそらくショートカットキー「ctrl＋下矢印キー」で最終行に移動した後、下矢印キーで最終行の1つ下のセルに移動するはずです。これが自動でできるようになると、データが追加された後でも毎回同じように最終行が取得できるようになり、新たなデータの追加作業や既存のデータの変更作業を全て自動化することができます。
また、データの最終行を自動で取得できるようになると、手動でデータの範囲を調整する必要がなくなり、データの集計作業やグラフを作成する際に柔軟で自動化された対応が可能となります。これが、最終行を選択する必要がある理由なのです。
データが追加されたとしても、きちんと表の一番最後の場所を認識してくれる。そんなマクロを一緒に作っていきましょう。
【基礎】実際にVBAで最終行を選択してみよう（上から下がるパターン）
まずは、表の一番最後を選択するマクロを作成してみましょう。
データが取得されている最後のセルを取得するには、Endプロパティを使用します。
Endプロパティは、Rangeオブジェクトの一部です。このプロパティは指定したセルや範囲において特定の方向（上、下、左、右）を指定し、末尾のセルを取得します。
Endプロパティの構文と引数は次の通りです。
Endプロパティ
Range.End(Direction)
Directionは、取得する末尾のセルの方向を指定する引数で、次の4つの内のいずれかの定数を使用することができます。
| で定数の名前 | 意味 |
|---|---|
| xlUp | 上方向にセルを探します。 |
| xlDown | 下方向にセルを探します。 |
| xlToLeft | 左方向にセルを探します。 |
| xlToRight | 右方向にセルを探します。 |
では、実際に例題を解いてみましょう。
【基礎例題1】
以下の表において、品名(B列)の最終行のセルを選択するマクロを作成してください。ただし、マクロ実行前はB5セルが選択されているものとします。
解答（マクロ）
Sub 基礎例題1()
Range(“B5”).End(xlDown).Select
End Sub
基礎例題1の解答（マクロ）実行後の画面
Endプロパティで一番下まで下がる!
プログラミング言語全てに当てはまるのですが、VBAは曖昧な表現が許されません。そのため、やりたいことを伝える場合、100人聞いたら100人が全く同じ作業をイメージできるような伝え方をする必要があります。
今回の場合、「最終行を選択する」をより詳しく言い換えると、
「B5セルから、最後のセルまで下方向に移動し、選択する」となります。
同じ内容をVBAに置き換える場合、
B5セル→：Range(“B5”)
最後のセルまで下方向に移動→.End(xlDown)
選択する→.Select
後はこれらを、オブジェクト式※や階層構造のルールに従って順番に並び替えてあげると完成です。
下方向に移動するときにEndプロパティを使用するのがポイントです。
- 「オブジェクト式って何？」と思った方は下記の動画をご覧ください。
【基礎】実際にVBAで最終行を選択してみよう（底から上がるパターン）
これまで、Endプロパティを使うことによって、スタート地点のセルから下方向に下りることで終端セルを取得する方法を解説してきました。そしてこの終端セルを取得する方法ですが、実はもう一つあります。それは、Excelの底に一旦降りてからもう一度上がっていくという方法です。
なんで上がる必要があるの?
なぜそんなややこしいことをする必要があるのでしょうか？
それは、最初にご紹介した方法では、表の途中に空白セルがあった場合、その表の一番最後にたどり着くことができないからです。例えば、B16セルのデータを削除して、この部分に空白があったとします。
この状態でEndプロパティを使って、表の一番最後にたどり着くためのマクロをもう一度実行してみましょう。すると、一番下のセルまで辿り着かず、B15セルの場所で止まってしまいます。つまり、Endプロパティを使ってスタート地点から下がっていく方法だと、表の途中に空白のセルがあった場合、空白セルの1つ手前のセルで止まってしまうのです。
そのため一度エクセルの底、つまり、一番下の部分に降りてからもう一度上に上がっていく方法によって終端セルを取得する方法が必要になります。
【基礎例題2】
以下の表において、品名(B列)の最終行のセルを選択するマクロを作成してください。ただし、マクロ実行前はB5セルが選択されているものとします。
解答（マクロ）
Sub 基礎例題2()
Range(“B30”).End(xlUp).Select
End Sub
下から上へ上がる!
今回の場合、B5セルから下方向に移動すると、B16セルが空白のため手前のB15セルで移動がストップしてしまいます。そのため、「下の部分に降りてからもう一度上に上がっていく」方法を使う必要があります。
具体的に表現すると、
「空白セルより下のセル（B30セル）から、最後のセルまで上方向に移動し、選択する」
となります。
また、同じ内容をVBAに置き換えると、
B30セル→Range(“B30”)
最後のセルまで上方向に移動→.End(xlUp)
選択する→.Select
となります。
後はこれらを並べ替えてあげると完成です。
Endプロパティの引数にxlDownではなくxlUpを使用するのがポイントです。
“底” の探し方について
しかし、この方法には問題があります。先程はB30セルを決め打ちで指定しましたが、B30セルより下にデータが追加されると、対応することができなくなってしまいます。
では、エクセルの底の場所をそのまま指定すればいいのかというと、そうではありません。
なぜなら、エクセルのバージョンによって、最終行の数値というのは異なるからです。例えば、最新のバージョンだと104万8576行目ですが、少し前のバージョンは最終行の数が6万5000行でした。
そのため、セルの場所というのを直接指定するのではなく、一番最後の場所を抽象的に指定する必要があります。
では、一体どうすれば良いのでしょうか？答えを先に言ってしまうと、Rows.Countを使って、Excelの底に一旦降りてから上がっていくことで実現できます。
どういうことなのか、実際に例題を解きながら確認してみましょう。
【基礎例題3】
以下の表において、品名(B列)の最終行のセルを選択する、データが追加されてもずっと使い続けられるマクロを作成してください。
解答（マクロ）
Sub 基礎例題3()
Cells(Rows.Count, 2).End(xlUp).Select
End Sub
Rows.Countで”底” を見つける!
条件が追加されたため、先程のように特定のセルを決め打ちすることができなくなりました。そこで、Rows.Countの出番です。
Rows.Countは、ワークシート内の行数を取得します。つまり、この部分は行の合計数を表します。
これにより、「2列目（B列）の最終行から、最後のセルまで上方向に移動し、選択する」
というような抽象的な指示がVBAでできるようになります。
後は今までと同じように、
2列目（B列）の最終行→Cells(Rows.Count, 2)
最後のセルまで上方向に移動→.End(xlUp)
選択する→.Select
と置き換えてから並び替えてあげると完成です。Rows.Countを使って、最終行を抽象的に指示してあげるのがポイントです。
【実践】終端セルの ”1つ下”を選択する方法について
実務で直面する「終端セルの1つ下にデータを追加する」を解決するために、終端セルの ”1つ下”を選択する方法について学んでいきましょう。終端セルの一つ下のセルへ移動するには、今まで書いたコードに対してプロパティを1つ追加するだけで実現できます。そのプロパティの名前は、Offsetプロパティです。例題を解きながら、Offsetプロパティを組み合わせてどんなふうにコードを変えていくか見ていきましょう。
【実践例題】
以下の表において、品名(B列)最終行セルの1つ下のセルに対して、文字列 ”鉛筆” を入力するマクロを作成してください。
解答（マクロ）
Sub 実践例題()
Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = “鉛筆”
End Sub
Offsetプロパティで ”1つ下”に移動する!
「Cells(Rows.Count, 2).End(xlUp).」までは基礎例題3と同じですが、後ろにOffsetプロパティが追加されています。
Offsetプロパティは、現在選択されているセルから指定された行数と列数だけ離れたセルに移動するよう指示することができます。
Offsetメソッドの基本構文と引数は次のとおりです。
Offsetメソッド
Offset(RowOffset, ColumnOffset)
| 引数の名前 | 引数の省略 | 引数の役割 |
|---|---|---|
| RowOffset | ⚪︎ | セルを何行分上下に移動するかを指定します。正の値を指定すると下方向に移動し、負の値を指定すると上方向に移動します。 |
| ColumnOffset | ⚪︎ | セルを何列分左右に移動するかを指定します。正の値を指定すると右に、負の値を指定すると左に移動します。 |
今回のマクロでは、Offsetプロパティによって2列目（B列）の最終行から1行下に移動しています。後は「.Value = “鉛筆”」を付け加えてあげれば、選択したセルに「鉛筆」という文字列を入力することができます。
Offsetプロパティは実務で頻出のプロパティです。終端セルを取得する以外の場面でもよく使うため、役割と使い方についてしっかり理解しておきましょう。
まとめ
今回は、VBAで最終行を取得する方法について解説しました。
今回の重要ポイントは３つです。
- 終端セルを取得するにはEndプロパティをうまく活用する
- 上から下へ下がるのではなく、Rows.Countで底を見つけてから上がっていく
- 終端セルの1つ下に移動したい場合は、Offsetプロパティを活用する
特に2つ目のポイントは、抽象的な考え方ですがとても重要なことです。
実務では新しいデータが追加されたり、データの一部が空欄になっていることが多いです。この場合、上から下がる方法を使ったり、特定のセルを底に指定してしまうと正しく終端セルが取得できないことがあります。そんな時はこの記事で学んだことを思い出して、Excelの底に一旦降りてからもう一度上がってみましょう。
また、
「もっとVBAを学びたい」
「わからないところは質問しながら体系的に学びたい」
そんな方はぜひユースフルのExcelPro講座VBAコースをご確認ください。現役エンジニアが開発、MicrosoftMVPが監修した実務直結のオンライン動画講座です。これさえ受講すれば、コードを書くたびにつまづいて検索して時間を消費してしまう・・・そんな状況がガラっと変わります！
