Endプロパティで上下左右の最終セルを取得

こんにちは　じゅんぱ店長　(＠junpa33)　です。

エクセルVBAでプログラムを組み立てる時に、ほぼ必ず使用するんじゃないかな？というコードで、

ワークシート上で、使用しているセルの最終端を取得する（掴む）ためのコードがあります。

データ入力の最終行番号を取得するコードもその1つです。

この最終端を掴むためのコードのKEYとなるプロパティが「Endプロパティ」です。

今回はこの「Endプロパティ」についての話です。

ちなみに「Endステートメント」というものもあります。こちらは「同名の別人」ですので、間違わないでください。「同名の別人」は改めてまた別記事で紹介させていただきます。

Endプロパティの記述方法

Endプロパティは、データが入力されている範囲の終端セルを取得します。

Endプロパティはデータ入力の最終行番号を取得するためによく出てきますが、

Endプロパティは戻り値としてRangeオブジェクトを返しますので、

行番号取得だけではなくて、色々な関連プロパティを使用することが出来ます。

（例えば、これからの説明では、取得したRangeオブジェクトに背景色を設定します。）

Endプロパティで上下左右の最終セルを掴む

xlUpとxlDownでの上下方向での最終行と、xlToLeftとxlToRightでの左右方向での最終列の掴み方を分けて説明していきます。

上下の最終セル（最終行）を掴む

• xlUp（上方向）を使って最終セルを掴む
• xlDown（下方向）を使って最終セルを掴む

サンプルとしてデータが書かれたこのワークシートを使います。

参考記事

Rowsプロパティについてはこちらを参考にしてください。

2022年5月5日 Rowsプロパティの働きと関連のメソッド

xlUp（上方向）を使ってデータ入力の最終セルを掴む

ワークシートの最終行から上方向に、データが入力されているセルをカウントダウンで見つけに行きます。

（上方向）A列に対してデータ入力の最終セルを見つけます。

見つけた最終セルは背景色を「黄色」に着色します。

Sub Endプロパティサンプル1_1()
    Cells(Rows.Count, 1).End(xlUp).Interior.Color = RGB(255, 255, 0)
End Sub

（上方向）B列に対してデータ入力の最終セルを見つけます。

見つけた最終セルは背景色を「緑色」に着色します。

Sub Endプロパティサンプル1_2()
    Cells(Rows.Count, 2).End(xlUp).Interior.Color = RGB(0, 255, 0)
End Sub

xlDown（下方向）を使ってデータ入力の最終セルを掴む

開始点となるセルをどこにセットするかで、結果が大きく変わります。

（下方向）A列に対してデータ入力の最終セルを見つけます。

A列の始点セルとして　セルA1 [ Cells(1,1) ]　を指定します。

見つけた最終セルは背景色を「青色」に着色します。

Sub Endプロパティサンプル2_1()
    Cells(1, 1).End(xlDown).Interior.Color = RGB(0, 0, 255)
End Sub

（下方向）B列に対してデータ入力の最終セルを見つけます。

B列の場合は、始点セルをどこに設定するかで、結果が違ってきます。

始点セルを　セルB1 [Cells(1,2)]　にした場合

見つけた最終セルは背景色を「赤色」に着色します。

Sub Endプロパティサンプル2_2a()
    Cells(1, 2).End(xlDown).Interior.Color = RGB(255, 0, 0)
End Sub

始点セルを　セルB3 [Cells(3,2)]　にした場合

見つけた最終セルは背景色を「紫色」に着色します。

Sub Endプロパティサンプル2_2b()
    Cells(3, 2).End(xlDown).Interior.Color = RGB(255, 0, 255)
End Sub

最終行の行番号を知る時は、同様にこのコードで知ることが出来ます。

Sub Endプロパティサンプル2_2c()
    MsgBox Cells(3, 2).End(xlDown).Row
End Sub

このコードはメッセージボックスで表示させるパターンです。

（下方向）C列に対してデータ入力の最終セルを見つけます。

C列の場合は途中の行にデータの空白がある場合です。

そのデータの空白が、「何も入れていない空」なのか「＝””」や「スペース」なのかで結果が違ってきます。

Sub Endプロパティサンプル2_2d()
    Cells(3, 3).End(xlDown).Interior.Color = RGB(0, 255, 255)
End Sub

「何も入れていない空」の場合

「＝””」や「スペース」　空白をデータとして入力した場合

左右の最終セル（最終列）を掴む

• xlToLeft（左方向）を使って最終セルを掴む
• xlToRight（右方向）を使って最終セルを掴む

参考記事

Columnsプロパティについてはこちらを参考にしてください。

2022年6月8日 Columnsプロパティでセルの列を指定する

xlToLeft（左方向）を使ってデータ入力の最終セルを掴む

ワークシートの最終列から左方向に、データが入力されているセルを見つけに行きます。

（左方向）1行目に対してデータ入力の最終セルを見つけます。

見つけた最終セルは背景色を「黄色」に着色します。

Sub Endプロパティサンプル3_1()
    Cells(1, Columns.Count).End(xlToLeft).Interior.Color = RGB(255, 255, 0)
End Sub

（左方向）3行目に対してデータ入力の最終セルを見つけます。

見つけた最終セルは背景色を「緑色」に着色します。

Sub Endプロパティサンプル3_2()
    Cells(3, Columns.Count).End(xlToLeft).Interior.Color = RGB(0, 255, 0)
End Sub

xlToRight（右方向）を使ってデータ入力の最終セルを掴む

開始点となるセルをどこにセットするかで、結果が変わります。

（右方向）1行目に対してデータ入力の最終セルを見つけます。

1行目の始点セルとして　セルA1 [ Cells(1,1) ]　を指定します。

見つけた最終セルは背景色を「青色」に着色します。

Sub Endプロパティサンプル4_1()
    Cells(1, 1).End(xlToRight).Interior.Color = RGB(0, 0, 255)
End Sub

（右方向）2行目に対してデータ入力の最終セルを見つけます。

2列目の場合は、始点セルをどこに設定するかで、結果が違ってきます。

始点セルを　セルA2 [Cells(2,1)]　にした場合

見つけた最終セルは背景色を「赤色」に着色します。

Sub Endプロパティサンプル4_2a()
    Cells(2, 1).End(xlToRight).Interior.Color = RGB(255, 0, 0)
End Sub

始点セルを　セルB2 [Cells(2,2)]　にした場合

見つけた最終セルは背景色を「紫色」に着色します。

Sub Endプロパティサンプル4_2b()
    Cells(2, 2).End(xlToRight).Interior.Color = RGB(255, 0, 255)
End Sub

（右方向）4行目に対してデータ入力の最終セルを見つけます。

6行目の場合は途中の行にデータの空白がある場合です。

そのデータの空白が、「何も入れていない空」なのか「＝””」や「スペース」なのかで結果が違ってきます。

Sub Endプロパティサンプル4_2c()
    Cells(4, 1).End(xlToRight).Interior.Color = RGB(0, 255, 255)
End Sub

「何も入れていない空」の場合

「＝””」や「スペース」　空白をデータとして入力した場合

xlToRight（右方向）を使った時の始点と空白セルの関係を考察する

xlToRight（右方向）を使った場合の始点セルの扱い（認識）と空白セルの扱いには注意が必要です。

下のサンプルで、A列を始点とした場合に

「2行目」、「4行目」と「6行目」ではEndプロパティがどのように反応するでしょうか？

2行目のVBAコード

Endプロパティが示すセルの背景色を青色にします。

Sub Endプロパティサンプル5_1()
    Cells(2, 1).End(xlToRight).Interior.Color = RGB(0, 125, 255)
End Sub

4行目のVBAコード

Endプロパティが示すセルの背景色を黄緑色にします。

Sub Endプロパティサンプル5_2()
    Cells(4, 1).End(xlToRight).Interior.Color = RGB(125, 255, 0)
End Sub

6行目のVBAコード

Endプロパティが示すセルの背景色を青紫色にします。

Sub Endプロパティサンプル5_3()
    Cells(6, 1).End(xlToRight).Interior.Color = RGB(125, 0, 255)
End Sub

3つのコードを実行するとこのようになります。

すべて同じC列を示しました。

Endプロパティで最終セルを掴む　まとめ

データの新規入力は、多くの場合は行の上から下へ、列の左から右へということですので、

Endプロパティを利用する時は、

ワークシートの最終行から上方向へ、最終列から左方向へという使い方が中心になると思います。

そういう意味では、「xlToRight」と「xlDown」は知っておく程度で良いのかもしれません。

いずれにしても、EndプロパティはエクセルVBAで重要なコードですので、しっかり使えるようになっておきましょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web