VBA Endプロパティで上下左右の最終セルをつかむコード

vbaendpropertyeyecatch

エクセルVBAで、データ入力したセルの終端を調べるという作業が良くあります。
最終行番号を取得するためにも、Endプロパティで最終セルを取得する必要があります。

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

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

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

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

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

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

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

Endプロパティの記述方法

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

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

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

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

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

構文
  •  Rangeオブジェクト.End(処理方向)

処理方向はこのようになります。

処理方向説明記述方法
xlUpシート最下端行から上方向へEnd(xlUp)
xlDown始点セルから下方向へEnd(xlDown)
xlToLeftシート最右端列から左方向へEnd(xlToLeft)
xlToRight始点セルから右方向へEnd(xlToRight)

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

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

上下の最終セル(最終行)を掴む

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

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

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

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

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

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

行番号を取得する場合はコードをこのように記述します。

「 Cells(Rows.Count, 1).End(xlUp).Row 」

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

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

セルにデータが入っているかどうかを判断するので、

罫線が引かれているかどうか(書式設定されているかどうか)には無反応です。

<strong>Endプロパティサンプル1_2</strong>
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) ] を指定します。

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

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

行番号を取得する場合はコードをこのように記述します。

「 Cells(1, 1).End(xlDown).Row 」

このA列の場合は、上方向「xlUp」でも下方向「xlDown」でも同じ結果を得ることが出来ました。

これが一番の基本形になります

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

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

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

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

始点セルが空白セルであった場合は、はじめてのデータ入力セルと出会うところでストップします。

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

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

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

セルB3を始点とすると狙い通りの位置のセルを掴むことが出来ました。

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

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

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

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

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

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

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

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

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

データの切れ目として空白セルを検知して途中でストップしました。

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

空白が入力されたデータとして認識され、目的の最終セルを取得することが出来ました。

左右の最終セル(最終列)を掴む

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

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

言葉の表現が難しいですが、

「シート最右端列から左方向に調べてセル範囲の一番右にあるセルを検出する」

という意味です。

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

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

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

列番号を取得する場合はコードをこのように記述します。

「 Cells(1, Columns.Count).End(xlToLeft).Column 」

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

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

セルにデータが入っているかどうかを判断するので、

罫線が引かれているかどうか(書式設定されているかどうか)には無反応です。

Endプロパティサンプル3_2
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) ] を指定します。

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

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

列番号を取得する場合はコードをこのように記述します。

「 Cells(1, 1).End(xlToRight).Column 」

このA列の場合は、左方向「xlToLeft」でも右方向「xlToRight」でも同じ結果を得ることが出来ました。

これが一番の基本形になります

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

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

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

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

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

最終行を掴むときと同様に、始点セルが空白セルであった場合は、はじめてのデータ入力セルと出会うところでストップします。

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

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

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

セルB2を始点とすると狙い通りの位置のセルを掴むことが出来ました。

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

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

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

Endプロパティサンプル4_2c
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プロパティが示すセルの背景色を青色にします。

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

4行目のVBAコード

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

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

6行目のVBAコード

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

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

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

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

  •  2行目はセルD2の空白セルを感知して、セルC2でSTOPしました。
  •  4行目はセルB4の空白セルを感知して、セルC4でストップしました。
  •  6行目はセルB6の空白セルを感知して、セルC6でストップしました。

つまり、始点セルの次のセルが

  • 空白でなかった場合は、初めて見つけた空白セルの手前のデータ入力セルでストップします。
  • 空白であった場合は、次のはじめてのデータ入力セルでストップします。

xldownについても同様です。

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

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

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

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

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

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

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできます。
Udemy1eyecatch エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

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

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