Excel VBA データが入力された最下行を取得して集計する

Pocket

（1）特定列は最下行まで空白の行がない。
（2）データ集計列には空白の行があるかもしれない。

上記の場合にデータが入力された列の最下行の1行下に集計式を入れるマクロを作りたいということはよくあると思います。

シンプルなVBA記述でマクロを作る方法です。

Contents

1 最下行に移動して集計(sum)をすること自体は簡単。
2 最下行まで空白行がない列(見出し列など)の最下行の値を取得する
3 Excel データが入力された最下行を取得して集計するVBA マクロ
- 3.1 共有:
- 3.2 いいね:

最下行に移動して集計(sum)をすること自体は簡単。

集計結果を表示したいセルを選択して、「=sum(」と入力してから、
集計したい範囲の列の最上行を選択して
shiftキーを押しながら
集計したい範囲の列の最下行を選択して、「)」と入力すれば

集計結果が表示されます。

同じ行の範囲で集計したい列がある場合は、
集計結果が表示されている（計算式が入力されている）セルを選択し、
ctrlキーとcキーを同時に押してコピーし
集計結果を表示したい列の同じ行のセルを選択して、
ctrlキーとvキーを同時に押してペースト（貼りつけます）。
複数の列に集計結果を表示したい場合は、
shiftキーを押しながら矢印キー（←か→）で複数のセルを選択してから
ctrlキーとvキーを同時に押してペースト（貼りつけます）。

集計結果を表示した行の合計を同じ行の右隣の列に表示したい場合は、
集計結果を表示したいセルを選択して、「=sum(」と入力してから、
集計したい範囲の行の最左行を選択して
shiftキーを押しながら
集計したい範囲の行の最右行を選択して「)」と入力すれば

集計結果が表示されます。

ここまでの作業、sum関数を使用した集計作業は日常的に何度も行なっているのではないでしょうか。

この作業の中で範囲指定などをマウスで行なっている方はキーボードだけで操作してみましょう。

何も難しいことはない、簡単な作業ではありますが、単純な作業をカチャカチャ・ガチャガチャとキーを叩きすぎるのはせわしないです。

この集計作業をマクロで自動実行できれば、ほんの少しですがせわしない作業から解放されます。

最下行まで空白行がない列(見出し列など)の最下行の値を取得する

今回の例ではA列（商品コード）の列は空白のセルはないということにしています。

A列は空白セルがありませんが、行数（商品アイテム数）はその都度変わります。
B列〜F列の5列は売上のない商品のセルはセルには何も入力されていません。

1行目は見出しですので、売上数のデータが入力されているのは2行目からです。
データが入力されている最終行は不明です。

あるセルを選択して、ctrlキーと矢印（↓）キーを同時に押せば、

データが入力されているセルであれば、データが入力されている最下行セルへと移動します。
データが入力されていないセルであれば、下の行の中でデータが入力されている最上行セルへと移動します。（データが入力されているセルが下の行の中になければsheetの最下行セルへと移動します。

ctrlキーと矢印（↓）キーを同時に押してセルの移動をすることをVBAでは「End(xlDown)」で表現します。

データが入力されているA列1行目から連続してデータが入力されているA列の最下行のセルは以下の表現です。

「Range(“a1”).End(xlDown).Row」

B列〜F列の5列は売上のない商品のセルはセルには何も入力されていませんので、
商品の売上がなかった空白のセルより下にデータが入力されていた場合は、
2行目〜「End(xlDown)では正しい集計ができません。

そこで、データが入力された最下行まで空白セルがないA列の最下行の行数を取得してB列〜F列の5列の売上を集計すれば正しく集計することにします。

Excel データが入力された最下行を取得して集計するVBA マクロ

（1）A列は最下行まで空白の行がない。
（2）データ集計列(B列〜F列の5列)には空白の行があるかもしれない。

上記の場合にデータが入力された列（B列〜F列の５列）の最下行の1行下に集計式を入れるマクロを作ります。

そして集計行が入った右隣のセルに集計結果の集計（総合計）式を入れてみました。

———————————————————————————-

Sub EndLine()

Dim EndLine As Long
EndLine = Range(“a1”).End(xlDown).Row + 1 ‘EndLine =　A列1行目から最下行＋１行目の行数

With Range(“b” & EndLine)
.Formula = “=SUM(b2:b” & (EndLine – 1) & ” )” 　　’SUM計算式を
.AutoFill Destination:=.Resize(1, 5) 　’AutoFillの到着地1を1から5へ変更
End With

Range(“g” & EndLine) = “=SUM(b” & EndLine & “:f” & EndLine & “)”

ActiveWorkbook.Save

End Sub

———————————————————————————-

(1) altキーとF11キーを同時に押して、VBEを起動します。

いつも使うという場合でしたら、VBA Project (PERSONAL.XLSB)の「標準モジュール」を右クリックして表示されるメニューから「挿入（N)」→「標準モジュール（M)」を選びます。

(2) Sub〜End Sub までをコピーしてペースト（貼りつけ）します。

(3) ctrl キーと s キーを同時に押して保存します。

(4) Excelに戻り、alt キーと F8 キーを同時に押して、マクロを呼び出します。

(5) 実行するマクロを選択して、alt キーと R キーを同時に押してマクロを実行します。

SUM関数による計算式が正しく入力されて、計算結果が表示されています。

【編集後記】

同じ結果を得るための方法は1つではありません。
Excel VBA マクロ実行の方法も同じく1つではありません。
データの最下行を取得するための考え方もさまざまです。
たとえば、sheetの最下行のセルを選択し、そこからctrlキーと矢印（↑）キーを押せば、データの最下行を取得できます。
しかし、集計したいデータの最下行のさらに下の方に集計するべきではない別のデータがあるかもしれません。
VBAでマクロを記述していくのに、どの考え方・表現が簡単でシンプルなのか、ご自身の好みも含めて、いろいろと試してみてはいかがでしょうか。

忙しいからせわしなくカチャカチャ・バチャバチャとキーを打ちまくるしかない、とならないように、
少しでも立ち止まって意味を考えて、それはどういう意味だろう（今回の例では、このデータの特長での最下行とはなんだろう）？と考えてみると、無駄な作業を見直すヒントが見つかるかもしれません。

今日の1日1新：タニタ食堂監修のデザートカスタードプリン

この記事を書いた人
最新の記事

小倉健二(労働者のための社労士・労働者側の社労士)Office新宿(東京都)

小倉健二（おぐらけんじ）労働者のための社労士・労働者側の社労士労働相談、労働局・労働委員会でのあっせん代理労災保険給付・障害年金の相談、請求代理相談・依頼ともに労働者の方に限らせていただいています。＜直接お会いしての相談は現在受付中止＞・mail・zoomオンライン対面での相談をお受けしています。 1965年生まれ57歳。連れ合い（妻）と子ども2人。労働者の立場で労働問題に関わって30年。 2005年(平成17年)12月から社会保険労務士(社労士)として活動開始。 2007年(平成19年)4月1日特定社会保険労務士付記。 2011年(平成24年)1月30日行政書士試験合格