【Excel VBA入門】まとめ｜最終行の取得とSUM関数マクロ

リストの最終行を自動取得して集計する方法をご紹介します。

取引量によって表の形が変わるような売上データ集計などを想定してマクロを作成しています。
こちらの作成例の考え方は実務でも転用しやすい内容となっているかと思いますので、ぜひご活用ください。

こちらでご紹介するおもな内容です。

最終行の取得

こちらのワークシートをつかってご説明します。

C列の「数量」を集計したいと思います。

基準となる列を決める

まず、どの列を基準として最終行を取得するかを考えます。

今回の例では入力箇所に空白などがなくすべての値が入力されていますが、必ずしもそういったリストばかりではありません。

ここで重要になってくるのが「もっとも空白になる可能性が低い要素」を選ぶことです。

たとえば、A列の「番号」が”通し番号”であった場合、データが追加されるたびに自動的に番号がわりふられるため、A列が空白になる可能性はありません。

そのため、今回の例の場合では「A列」を基準とします。
いまのところ最終行は”6行目”ですね。
もし仮にB列の「品目」を基準にした場合、データの入力モレなどによる空白セルによって、最終行は”５行目”となってしまいます。

”通し番号”とは、「初めから終わりまで一続きにつける番号」のことです。

基準列の最終行を取得する

では「A列」を基準行としましたので、こちらの最終行を取得します。

手順はつぎのとおりです。

コードとしてはこのようになります。

    Dim lastrow As Long
        lastrow = Cells(Rows.Count, 1).End(xlUp).Row

手順1｜Dim lastrow As Long｜1行目

「変数：lastrow」を整数型として定義しています。

手順2｜Cells(Rows.Count, 1)｜2行目

「Cells」のカッコ内の左側にある行番号を指定する箇所に「Rows.Count」と記述しています。
こちらは、「Rows：行」の「Count：数」で「行の数」を意味しています。
キーボードをつかってセルを選択するときに、空白列で「ctrl」と「下方向」の同時押しをしたときに選択される行です。
ちなみに、わたしのExcelの場合は「1048576行」が選択されます。

また、「Cells」のカッコ内の右側にある列番号を指定する箇所に「1」と記述しています。
こちらはさきほどの理由により、”通し番号”の「A列」を指定しています。

手順3｜End(xlUp)｜2行目

「End」プロパティをつかって、もっとも下のセルから「上方向」にセルを移動させます。

最初に値が入力されているセルまでいっきに移動します。
キーボード操作の場合、「ctrl」と「上方向」の同時押しでおなじ動作ができます。

ちなみに、「End」の引数は2種類あります。

手順4｜Row｜2行目

「Row」プロパティをつかって取得したセルの「行番号」を取得します。

取得したセルは「A6」ですので、取得する行番号は「6」です。

最下行のひとつ下を取得する

今回の目的は、C列の「数量」を集計することでした。

さきほど取得した行番号は「6」ですので、ここのひとつ下に集計値を入力したいと思います。

行番号「6」のひとつ下は、行番号「7」です。
ですので、行番号に「1」を加えましょう。

コードはこちらです。

    Cells(lastrow + 1, 4).Value = "←合計"

「Cells」のカッコ内の左側にある行番号を指定する箇所に「1」を足しました。
これでひとつしたの行を指定することができます。

また、「Cells」の右側の列番号「4」、つまり「D列」に集計結果がわかりやすいように目印を入力する設定にしておきます。

合計値を入力する｜VBA関数

合計値を取得するためにVBA関数をつかいます。

ここでは、おなじみかと思いますが「SUM関数」をつかってコードを書きます。

VBA関数

基本的なつかい方はこちらです。

こちらに今回のワークシート関数「SUM関数」をあてはめていきます。

    Application.WorksheetFunction.Sum(Range(Cells(2, 3), Cells(lastrow, 3)))

Application.WorksheetFunction.Sum()

今回は「SUM関数」を指定しています。

これはVBA関数をつかうときの決まりですので、ぜひ覚えておきましょう。
全部を丸暗記するのではなく、あとでインターネットで検索して見つけられるレベルになれば問題ないと思います。

Range(Cells(2, 3), Cells(lastrow, 3))

こちらでは「Range」のなかに「Cells」が2つ格納されています。

ひとつずつ確認をしておきましょう。
まず1つ目「Cells(2, 3)」は「セルC2」をあらわしています。
つぎに2つ目の「Cells(lastrow, 3)」では、さきほど取得した最下行が「6」ですので、「Cells(6,3)」つまり「セルC6」をあらわしています。

これを組合わせて”「セルC2」から「セルC6」の範囲”を指定しています。

まとめ

それでは最後に今回ご紹介したコードをあらためて確認しましょう

Sub sample2()
    '最終行を取得する
    Dim lastrow As Long
        lastrow = Cells(Rows.Count, 1).End(xlUp).Row

    '最終行のひとつ下を取得する
    Cells(lastrow + 1, 4).Value = "←合計"

    '最終行のひとつ下に合計値を入力する
    Cells(lastrow + 1, 3).Value = Application.WorksheetFunction.Sum(Range(Cells(2, 3), Cells(lastrow, 3)))
End Sub

実行結果はこちらです。