入力されているデータの最終セルを取得する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

これは基本的なテクニックだと思って今まで解説しませんでしたが、セミナーなどで質問が多かったので、簡単に書いておきます。たとえば、下図のようなリストがあったとします。

この、現在入力されている最終セル(あるいは最終行)を取得するには、どうしたらいいでしょう。

最終セルを取得する

Excelに詳しくない、VBAのプログラミング的なことしか知らないような方でしたら、次のように考えるかもしれませんね。「A列を上から順番に見ていって、空欄セルが見つかったら、その直前が最終セル」だと。その方法でやってみましょうか。

Sub Sample1()
    Dim i As Long, r As Long
    For i = 1 To 10000
        Range("A" & i).Select
        If Selection = "" Then
            r = i - 1
            Exit For
        End If
    Next i
    Range("A" & r).Select
End Sub

思いっきり下品なコードにしてみました(笑)。まぁ、ここまではヒドくないと思いますけど。

さて、ExcelにはEndモードという機能があります。Endモードに切り替えるには、Endキーを押します。実行すると、ステータスバーに「End」または「Endモード」と表示されます。

【Excel 2003まではステータスバーの右下】

【Excel 2007以降はステータスバーの左下】

Endモードの状態で矢印キーを押すと、アクティブセルが、データが入力されている終端にジャンプします。

アクティブセルが、データの入力されていない範囲にあったときも、Endモードでデータが入力されている終端にジャンプします。

この「データの外から、データが入力されている方向にジャンプする」操作をマクロ記録してみましょう。

Sub Macro1()
    Range("C14").Select
    Selection.End(xlUp).Select
End Sub

2行目のSelectionは「選択されたセル」ですから、その前で選択しているセルC14(Range("C14"))です。ですからこれは、次のように1行で表せます。

Sub Macro1()
    Range("C14").End(xlUp).Select
End Sub

このように、データが入力されていないであろう十分下のセルから、上方向に向かってEndモードでジャンプすれば、データが入力されている最終セルを簡単に取得することができます。Excelの機能を熟知している人なら、このように1行で実現できるんです。Excelの機能に詳しくない、VBAのプログラミング的なことしか知らない人からVBAを習ってはいけない、という良い例ですね。

では、どこから上に向かってジャンプすればいいのでしょう。一般的には、ワークシートの最終行からジャンプすることが多いです。Excel 2003までなら65536行のセル、Excel 2007以降でしたら1048576行のセルです。しかし、Excelのバージョンによってワークシートの大きさは異なりますし、1048576なんて変な数字を暗記するのは面倒です。

そこで、ワークシートの最終行セルを、VBA的に特定します。セルを特定するときは、RangeまたはCellsを使います。このCellsは「Cells(行, 列)」と、行位置と列位置を数値で指定します。今回、列位置は、最終セルを調べたい列ですから、あらかじめ分かっています。たとえばA列としましょう。最終行というのは、つまり「ワークシートに何行あるか」ということです。これは「ワークシートにある行の総数」とも言えます。ワークシートの全行は、Rowsコレクションで表されます。このコレクションに含まれる要素(行)の個数は、Countプロパティで分かります。したがって、ワークシートの最終行にあるセルは「Cells(Rows.Count, 1)」ということになります。これなら、Excel 2003までで実行すれば「Rows.Count」が自動的に65536を返しますし、Excel 2007以降なら1048576となります。

リストの最終セルを選択するには、次のようにします。

Sub Sample1()
    Cells(Rows.Count, 1).End(xlUp).Select
End Sub

もちろん、選択しないで、直接操作することも可能です。この場合、当たり前ですがアクティブセルは移動しません。

Sub Sample2()
    Cells(Rows.Count, 1).End(xlUp).Font.ColorIndex = 3
End Sub

最終セルの行数だけを知りたいのでしたら、Endモードで見つかるセルの行番号を取得します。

Sub Sample3()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox "最終行は" & LastRow & "です"
End Sub

最終セルの1つ下のセルを取得する

一般的に、リストの最終セルを取得する目的は、おおむね次の2つです。

  • データが入力されている範囲を操作する
  • 最終セルの下に新しいデータを入力する

For Nextの繰り返しなどで、データが入力されている範囲を自動的に判別したいときは、Endモードでジャンプしたセルの行番号を取得すればいいです。

Sub Sample4()
    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 1) に対する処理
    Next i
End Sub

そうではなく、最終セルの下に、新しいデータを入力する場合は、最終セルの1つ下のセルを特定しなければなりません。最終セルがどこかはマクロを実行するまでわかりませんので、アドレスで指定することはできませんが、上のように最終セルの行番号を取得できるのですから、それに＋1すれば最終セルの1つ下のセルがわかります。たとえば、次のようなコードです。

Sub Sample5()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(LastRow, 1) = "2010/1/19"
End Sub

もちろん、これでもいいのですが、ここではOffsetプロパティを覚えましょう。Offsetプロパティは「あるセルから見て、○行△列移動した位置にあるセル」を返すプロパティです。書式は次の通り。

あるセル.Offset(行, 列)

たとえば、セルC3から見て3行下で、2列右にあるセルは、セルE6です。これをOffsetプロパティで表すと

Range("C3").Offset(3, 2)

となります。

あるセルから見て「1つ下のセル」とは、「1行下で、0列右のセル」です。したがって、最終セルの1つ下のセルは

最終セル.Offset(1, 0)

となります。最終セルは、Endモードを使って取得しますから、次のように表せます。

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

ちなみに、Offsetプロパティでは、引数の「行」と「列」を省略することができます。省略すると"0を指定した"とみなされますので、「Offset(1, 0)」と「Offset(1)」は同じことになりますし、「Offset(0, 1)」は「Offset(,1)」とも書けます。書けますが、省略しない方がいいです。理由は、ソースの可読性を高めるためです。

「Offset(1, 0)」というコードなら、これは「行が1で列が0」と読めます。ところが「Offset(1)」というコードを読むためには「本来Offsetは行と列の2つを指定する」「しかしここには1つしか引数がない」「カンマがないということは、この引数は先頭の引数だ」「先頭の引数は行だから行が1だ」「列の引数は省略されているので0だ」と思考しなければなりません。どっちが分かりやすいって、省略しない方が分かりやすいのですから、省略しない方がいいです。それだけの理由です。0を書くメリットはあります。しかし、0を省略するメリットはありません。もちろん、キーボードから0のキーを探すのに数十分もかかったり、0のキーが超重たくて両手で力を入れなければ入力できないのでしたら、どうぞ省略してください。

閑話休題。

Offsetプロパティを使うと、リストに新しいデータを追記するときに便利です。

という位置関係になりますので、新しいデータを追記するには、次のように書けます。

Sub Sample6()
    With Cells(Rows.Count, 1).End(xlUp)
        .Offset(1, 0) = "2010/1/19"
        .Offset(1, 1) = "佐山"
        .Offset(1, 2) = 1234
    End With
End Sub