マクロを使うとデータの最終行と最終列を認識出来る様になる
本記事ではデータの最終行とデータの最終列を取得する為のコードを紹介し解説していきます。
矢印の方向には意味があります。話題の「Excelの底」も出てきます。
データの最終行と最終列が認識できる様になると良いことがたくさんあります。
都度データ範囲が違ったり飛び地がある様なデータにも対応できるコードが書けるようになりますよ。
今回の記事の中でキーとなる要素「Cellsプロパティ」についても一緒に勉強していきます。
前回の記事
前回はRangeオブジェクトを使いオブジェクトとプロパティの関係について説明しました。
VBAを勉強するうえで必ず通らないといけない道です。嫌にならない様に少しずつ出していきます。
本記事での課題
今回の課題です。
表（リスト）の最終行と最終列を取得出来る様にする
列と行について
先にお伝えしておきますがあえて列行と書いています。ご了承ください。
前回記事の中では仕事をする範囲が定まらない時にどうしようか？というところで話が終わっていました。
皆様はワークシートの中で作業範囲を決める時にどうやって確認していますか？
マウスをクルクル・・・セルにカーソルを合わせて・・・という感じででどこまでが作業範囲なのか自力で探しますよね。
先に結論を申し上げますとマクロでも同じことをやります。作業範囲はマクロに認識してもらいましょう。
そのためにはまず列と行という2つの要素の位置関係を確認しておく必要があります。
あらためて列と行の確認です
まずは知識を整理します。そもそも列と行を理解できているのか確認させていただきます。
慣れている方は信じられないかもしれませんが列と行を逆に認識している人もけっこう居るんですよ。
上記が列と行です。この情報をコードに展開してみましょう。
Range(“A1”)という書き方ではパラメータとして用意するかっこの中の引数を見ると(“列行”)の順になってますよね。
冒頭で「列行」の順で文字を書いたのはこの並びに合わせた為です。
Rangeオブジェクトを使う時はパラメータ（引数）の並びは列行の順番だと覚えておいてください。←重要です
Cellsプロパティ
続いてCellsプロパティです。Micosoftのドキュメントから定義を引用します。
ワークシートのすべてのセル (現在使用されていないセルも含む) を表す Range オブジェクトを返します。
Microsoft：Worksheet.Cells プロパティ (Excel)
・・・多分今？が出ていますよね。私は初心者の時にこの言い回しが何を言っているのか全く分かりませんでした。
まずは深く考えず「CellsプロパティからRangeオブジェクトを指定できる」と思っておいてください。
以降Cellsプロパティの説明をする中でRangeオブジェクトと比較しながら説明していきます。
読んでいただくと今回のタイミングでなぜCellsプロパティを勉強しないといけないのかが分かってきます。
使い方（使用例）
Microsoft のドキュメントから構文を引用するつもりでしたが非常に分かりにくいので使用例を引用します。
日本語と合わせてコードを見ていただくとなんとなく理解する事ができるはずです。
コード内のCells(5, 3)という表記がセルC5を表しています。なぜそうなるのか（仕組み）を次のブロックで説明します。
作業中のブックの Sheet1 のセル C5 のフォント サイズを 14 ポイントに設定します。
Worksheets(“Sheet1”).Cells(5, 3).Font.Size = 14
Microsoft ：Worksheet.Cells プロパティ (Excel)より抜粋
Cellsプロパティは行が先
まずは見た目から入ります。CellsプロパティはRangeオブジェクトとパラメータの位置が逆になります。ご注意ください。
パラメータの位置は逆になりますがRangeオブジェクトでもCellsプロパティでも同じ様にセルを表現できます。
- Cellsプロパティは Cells(行,列) という建付けになっています
- CellsプロパティはRange(”列行”)の形を返します
ポイントで書いた内容を画像で表現してみました。詳細は以下画像で確認ください。
順番さえ間違えなければCellsプロパティで書いたコードも結果としてはRangeオブジェクトと同じ値を返す事が出来ます。
そうなるとRangeオブジェクトだろうがCellsプロパティだろうが「どちらでも良いのでは」と考えてしまいます。
一見どうでも良いと感じてしまう様なこの議論ですが実は明確な回答があります。
続いて今回の記事であえてCellsプロパティを勉強する理由を説明していきます。
RangeオブジェクトとCellsプロパティは使い勝手で選ぶ
RangeオブジェクトとCellsプロパティはそれぞれ適した使い時があります。あくまで一例ですが使い分けを紹介します。
今回はデータの最終行と最終列を取得するというミッションです。
データの最終行と最終列というのは一般的には都度変化します。よって今回はCellsプロパティを使う事になります。
- 毎回扱うセルの値が確定している時は Rangeオブジェクトを使う
- 毎回セルの値が確定していない時は Cellsプロパティを使う
続いてもう1つポイントを提示します。
- Range(“A1”) ・・・ かっこ内の引数は文字列
- Cells(1,1) ・・・ かっこ内の引数は数値
このポイントをもとにそれぞれの要素の使い勝手をを考えていきましょう。
Rangeオブジェクトのパラメータは文字列
毎回同じ行列数の表を使う時はRangeオブジェクトが適しています。
理由は毎回同じセルを選択するのでRangeオブジェクトのパラメータ（引数）を書き換える必要が無いからです。
セルに書く関数を使ったことがある人ならイメージ出来ますね。
ダブルクォーテーションで挟んだ文字は絶対値（文字列）です。
一般的にはダブルクォーテーションで挟んだ文字はコード内では書き換えないという使い方をしてください。
よって月毎に最終行が変わるようなリストに対してはRangeオブジェクトは適していない。という考え方をしましょう。
Cellsプロパティのパラメータは数値
Cellsプロパティを使うとコード内で行、列を変えやすくなります。
理由はCellsプロパティでセルを指定する時はパラメータが数値だという事です。（この数値は絶対値ではないです）
パラメータが数値だと扱いやすい理由は記事の最後に解説しています。（先に見たい人はこちら）
現段階ではパラメータが数値の方が変化に強いぐらいに考えておいてください。
よってデータの最終行、列の様に都度値が変化するデータにはCellsプロパティが適しているという考え方をしましょう。
Cellsプロパティは繰り返し処理や条件分岐との相性がよい
パラメータ（引数）が数値であることはメリットになります。繰り返し処理や条件分岐との相性も良くなります。
具体的な説明は別の事例をご覧ください。（条件分岐と繰り返し処理の記事はこちら）
ご注意ください
初心者様向けの記事は少ないのでこのシリーズが終わってから閲覧していただくことをおすすめします。
事例：最終行と最終列を取得する
事例を用意しました。列と行について検証を続けましょう。「リストの最終列行を取得」してみます。
ワークシート
以下のような表（リスト）を用意してみました。
空欄（空白セル）もありますが気にせずにこのデータを使っていきます。
本来は画面からは見えないセルを取得する為に作業をするのですが見易くする為小さいリストにしています。
20行×10列前後であれば小さいサイズのPCでも一画面で全部を見る事ができると判断しこの大きさにしました。
加えて分かりやすい様にセルに色を付けています。最終行は青、最終列は黄色の終端を数える事になります。
結論としては最終列は6、最終行の番号は15になります。
以降で具体的にどうやって列と行の番号を取得するのかを説明します。
最終的には神奈川県と表記されているセルF15を取得するまで説明する予定です。
今から勉強する方法を使えばリストの最終地点を取得する事が出来ます。
なぜ黄色、青色のセルの終端を数えることにしたのか
理由は「表の大きさが想定できる場所」だからです。まずは列から確認していきます。
多くの表は見出しの行が一番上（画像の黄色部）です。この行の終端をカウントしたら最終列が取得出来そうです。
続いて行です。表の一番左の列というのは一般的に情報が揃っていることが多いです。
一番左の列に情報が揃ってないデータもありますがレアケースです。今回は無視します。
表の一番左に来る要素というのは番号や品番、氏名のようなキーとなる情報が多いです。
基本的には最終行まで値が入っているデータのはずだという事にします。
上記のような理由から一番左の列を最終行を確認するための列に使います。
この時に途中で空欄（空白セル）が居てもよしとします。
とにかく最後の行に値が居る列を最終行を取得するデータとして使います。
そしてその確率が極めて高いのが表の一番左に来る列だという事（にします）です。
コード
コードはこちらになります。
- 列 = Cells(5, Columns.Count).End(xlToLeft).Column
- 行 = Cells(Rows.Count, 1).End(xlUp).Row
解説
このコードを1回で理解するのは難しいです。理解しやすい様に分解して見ていきましょう。
コード内の数値
今回は値が確定している数値から見ていきます。
行のコードの中に居る1は表の１列目(A列)を表現しています。Cells(行,列）なのでコンマの後ろの1は列です。
上の画像で見るとセルA5と青いゾーンが含まれたA列ということになります。
列のコードの中に居る5は表の見出し行である5行目を見ています。Cells(行、列）なのでコンマの前の5は行です。
上の画像で見るとセルA5と黄色ゾーンが含まれた5行目ということになります。
セルA5が緑になっているのは青と黄色が交わるので緑にしただけです。特に意味はありません。
コードを分解して考える
ひとまず最終行を取得する為のコードを使って解説していきます。最終列も考え方は同じです。
Cells(Rows.Count,1)
まずはコードの前半分を考えます Cells(Rows.Count,1) は「1列目の最終行」を示しています。
Cellsは行、列の並びでしたね。という事は「Rows.Count」までが行で「1」が列です。
以下画像はRows.Countというコードをメッセージボックスで表示させた結果です。
現在最新の拡張子.xlsxや.xlsmは1,048,576行目が最後の行なので以下の様な値が返ってきます。
Rows（行：複数）をカウントした結果 = 存在する行の個数 = 最終行です。という事です。
ここがExcelの底になります。
CellsプロパティはRangeオブジェクトを返します。
Cells(Rows.Count,1)はCells(1048576,1)となりRange（”A1048576”）という値を返してきます。
.End(xlUp)
後半は .End(xlUp) です。Endプロパティはデータの端のセルを返します。
分かりにくいのですが検索を始めて最初に値を見つけたセルをデータの端と認識します。
Endプロパティの後のかっこは検索方向を表す引数です。XlUpは「上方向」です。
全部まとめると「上方向に向かってデータの端を探す中で最初に認識したセルを返す」と言っています。
Range(“A1048576”)からxlUpで1行目方向に値を探しに行く事で結果表の一番外側の値を取得できます。
繰り返しますが（xlUp）は「1行目の方向に向かって」という指示になります。
列の時は(xlToLeft)を使っています。これは「1列目の方向に向かって」となります。
この様に方向は各状況によって指定出来ます。
今回行を取得する際は(xlUp）を採用しているという事です。
.Row
コードの最後です。.Row は「行番号を返して」という意味になります。
全部のコードを合わせて考えると以下の様になります。
表の1列目の終端となるセルの行番号を取得したいので
「最終行から上に向かいデータの端を探す中で最初に認識したセルの行番号を返して」となります。
今回のコードは取得したRangeオブジェクトから任意で決めた方向に向かって値を探しに行きます。
その中で一番最初に見つかったRangeオブジェクトの行番号が欲しいと書いています。
そうやって得られた値というのは「結果最終行だよね」という事になります。
さらに考える：画像、文字でイメージをつかむ
より分かりやすくする為に以下画像を使って行と列について個別に説明します。
行
再度コードを書いておきます。 行 = Cells(Rows.Count, 1).End(xlUp).Row です。
実際の表で考えてみましょう。1列目の行は5行目から始まってますがいったん置いておきます。
「1,048,576行目から1行目方向にデータを探す中で最初に値が見つかったセルの行番号だけをください」
という考え方です。
コードを実行した結果ここでは15行目が最終行という事になります。
よって先程のコードの戻り値は15となります。行番号なので欄外のグレーの値が返ってきます。
セルA15に入力されている10が返ってくるわけではないので注意してください。
あくまで行番号が返ってくるという事を忘れない様にしましょう。
列
列もコードを書いておきます。 列 = Cells(5,Columns.Count).End(xlToLeft).Column です。
列も考え方は行と一緒です。
「列方向のXFD列からA列方向にデータを探す中で最初に入力があるセルの列番号だけを教えてください」
という考え方です。
そうやって得られた値というのは「結果最終列だよね」という事になります。
この考えであればリストの外側からリストの範囲を決める事ができます。
途中に空欄が居ても画面から見えてなくても行列の最終地点を認識することが出来ます。
伏線回収：Cellsプロパティのパラメータが数値だと扱いやすい理由
行、列を取得するためのコードを使えば戻り値として数値が返ってくるという事がわかりました。
取得した数値を使って行と列の値を合わる事でリストの最終地点を認識する事にします。
最終行が15(15行目)と最終列が6(F列)という事が分かったのでコード内でCellsプロパティに当てはめてみましょう。
数値として取得した最終行と列をそのまま表の範囲（最終地点）に展開出来ているのが分かります。
Cellsプロパティの解説の中で「Cellsプロパティはパラメータが数値で扱いやすい」と書いたのはここにつながります。
Sub ワークシートの最終行と最終列()
Dim 行 As Long
Dim 列 As Long
行 = Cells(Rows.Count, 1).End(xlUp).Row
列 = Cells(5, Columns.Count).End(xlToLeft).Column
Cells(行, 列) = "最終地点"
End SubこちらはVBEの画像です。ローカルウインドウと一緒に見ることで変数が取得出来ていることがわかりますね。
ワークシートには「最終地点」という文字列が入力されました。
これで「セルF15がリストの最終地点」とVBAに認識してもらう事が出来たというわけです。
予告：取得した行と列を効率よく使うには変数が必要
しかしよく見るとコード内のCellsプロパティのパラメータ（引数）に行と列という漢字が入ってますね。
Cellsプロパティのパラメータは数値だったはずです。思考も見た目も非常に違和感があります。
この「行」と「列」という漢字はVBAの中に用意された万能な要素です。この要素は変数という機能です。
変数を使えば実行時に「都度値が変わるようなデータ」でも滞りなく仕事を流す事ができる様になります。
詳細は次の記事で紹介します。
まとめ
「都度データ範囲が違ったり飛び地がある様なデータにも対応できるコード」の一例を紹介しました。
不確定な値を掴むという案件をリストの最終地点を認識するという事例で再現した次第です。
- RangeオブジェクトとCellsプロパティの関係
- Endプロパティの使い方
- 画面から見えないセルも指定できる
Enjoy Excel
基本的に作業をするには始点と終点が必要です。
VBAに作業範囲を認識してもらわないとどんなコードを書けば良いのか定まりません。
VBAに作業してもらうには「○○から△△まで」と教えてあげる必要があります。
つまりVBAに限らずプログラミングでは都度「作業範囲を決める」という仕事があると思ってください。
次は実際にコードを書いてみる事にしましょう。
変数という機能の解説を積み残しているので次回記事に進んで内容を確認してください。
変数は不思議な機能です。難しいかもしれませんが覚えると世界が広がりますよ。
参考：おすすめ書籍
初心者様向けの書籍です。多くの書籍とは一風変わった事を書いてますが共感が持てたので紹介します。
「マクロを勉強するのは少しでも仕事を効率化させる為」であり
「マクロを完璧にマスターするわけではない」という考えのもと書かれた本です。