VBA-6マクロでエクセルシートの指定範囲から最終行を取得する

本記事ではデータの最終行とデータの最終列を取得する為のコードを紹介し解説していきます。

矢印の方向には意味があります。話題の「Excelの底」も出てきます。

都度データ範囲が違ったり飛び地がある様なデータにも対応できるコードが書けるようになります。

前回はRangeオブジェクトを使いました。

セルの範囲を指定する際範囲が決まってない時にはどうするのかを考えるところまで進みました。

オブジェクトとプロパティの関係について書きました。

VBAを勉強するうえで必ず通らないといけない道です。

嫌にならない様に少しずつ出していきます。

先にお伝えしておきますがあえて列行と書いています。ご了承ください。

仕事をする範囲が定まらない時（都度違う量のデータを扱う時）にどうしようか？

というところで前回記事の話が終わっていました。

手作業の時はみなさんどうやってますか？

マウスをクルクル・・・でどこまでが作業範囲なのか自力で探しますよね。

上記が列と行です。

Range("A1")という書き方はかっこの中をみると("列行")になってますよね。

だからあえて列行と書いていました。Rangeを使う時は列行だと覚えておいてください。

では事例を用意して列と行について検証を続けましょう。

どこが最終の列行なのか分からないリストの最終列行を取得してみます。

以下画像を例にリストの枠の大きさ（最終列と最終行）を確認するにはどうしたら良いのかを考えます。

前提として「リストの始点は毎回変わらない」という縛りをとらせていただきます。

実際の運用でも始点が変わる事は少ないです。変わるのは主に終点です。

以下画像だと緑色のセル（セルA4)は毎回変わらないという前提で話を進めますのでご注意ください。

（データの始点が変わる時のコードはここでは省略します）

どんなリストでもほとんどの場合は見出しの行が一番上（画像の黄色部）ですね。

ここをカウントしたら最終列が取得出来そうです。

行はデータテーブルの一番左の要素が番号とかだったら全部値が居そうな感じがしますね。（画像の青部）

場合によっては一番左の列が番号ではないデータもあります。

ただし一番左に来る要素というのは品番だったり氏名のようなキーとなる情報が多いです。

ほとんどは最終行に値が居るデータのはずだという（言い切って）事にします。

上記のような理由から一番左の列を最終行を確認するための列に使います。

この時に途中で空欄が居てもよしとします。

とにかく最後の行に値が居る列を最終行を取得するデータとして使います。

そしてその確率が極めて高いのが表の一番左に来る列だという事（にします）です。

リストの最終列と行を確認する場所を決めたところで今度は実際の作業に落とし込んでみます。

以下のようなリストを用意してみました。

空欄のところもありますがひとまずこんな感じでデータが入っているものを使います。

本来は画面からは見えないセルを取得するという目的でやってます。

実際はデータを大きくすると難しくなるので今回は小さいリストにしています。

リストの列と行を数える際の考え方は前項で書いた方法と同じです。

分かりやすい様にセルに色を付けておきました。

最終行は青、最終列は黄色の終端を数える事になります。

結論としては最終列は6、最終行の番号は15になります。

具体的にどうやって列と行の番号を取得するのかを考えていきます。

最終的には神奈川県と表記されているセルF15を取得するまで説明していきます。

今から勉強する方法を使えばリストの最終地点を取得する事が出来ます。

セルF15が空欄だったとしても大丈夫です。

以下のようにコードを書くことでまずは列と行別々にリストの最終地点を取得します。

その後取得した列と行を合わせて最終地点のセルを表示する様にします。

まずは以下コードをご覧ください。

コードの書き始めがRangeではないですね。Cells　から始まってます。

CellsプロパティはRangeオブジェクトを返すプロパティです。

最初のころ私は何を言っているのか全く分かりませんでした。

セルを表現する際はRangeオブジェクトを使っていたのに急にCellsプロパティが出てきました。

加えて難しい事も言ってます。

ここで一旦整理しましょう。実は少し前から「列行」ではなく「行列」で記事を書いています。

以降は行、列の順で書いていきます。

ややこしいですが注意してください。Cellsは行と列の並びがRangeとは逆です。

加えてCellsプロパティで書いたコードも結果としてはRange○○の値を返す事が出来ます。

毎回同じ行列数の表を使う時はRangeオブジェクトが使えます（適しています）。

理由は毎回同じセルを選択するのでRangeオブジェクトの引数を書き換える必要が無いからです。

セルに書く関数を使ったことがある人ならイメージ出来ますね。

ダブルクォーテーションで挟んだ文字は絶対値です。

一般的にはダブルクォーテーションで挟んだ文字は変えないという使い方をしてください。

よって月毎に最終行が変わるリストに対してRangeオブジェクトは適していません。

Cellsプロパティを使うとコード内で行、列を変えやすくなります。

理由はCellsプロパティでセルを指定する時は引数が数値だという事です。

絶対値ではないので扱いも簡単です。

引数が数値だと扱いやすい理由は以降で解説しています。このまま読み進めてください。

もう少し勉強していくと分かるのですが繰り返し処理など他の機能との相性も良いです。

扱いやすいのは分かったけど具体的には？と考える方も多いでしょう。

ここで先程提示したポイントを使います。

これです。最終行と最終列を取得する為のコードは数値を取得する事ができます。

数値という事はCellsプロパティの引数と同じです。

これがRangeオブジェクトを使わずCellsプロパティを使う理由です。

もう少し具体的に話します。

画像内の青、赤の四角の中に居る数値をコード実行時に取得します。

その値を範囲を指定するコードに展開します。

これが出来れば最終行と最終列が異なるリストに対応出来る様になります。

画像左の赤矢印で続いているコードは全部同じ範囲を示しています。

Cellsプロパティに数値を使う事でセルの範囲を設定できる事が分かります。

行のコードの中に居る1は表の１列目(A列)を最終行を認識する行にした場合です。

上の画像で見るとセルA5と青いゾーンが対象です。

列のコードの中に居る5は表の見出し行が5行目だった場合です。

上の画像で見るとセルA5と黄色ゾーンが対象です。

セルA5が緑になっているのは青と黄色が交わるので緑にしただけです。意味はありません。

まずはコードの前半分を考えます　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プロパティはデータの端のセルを返します。

分かりにくいのですが検索を始めて最初に値を見つけたセルをデータの端と認識します。

Endプロパティの後のかっこは検索方向を表す引数です。XlUpは「上方向」です。

全部まとめると「上方向に向かってデータの端を探す中で最初に認識したセルを返す」と言っています。

繰り返しますが（xlUp）は「1行目の方向に向かって」という指示になります。

列の時は(xlToLeft)を使っています。これは「1列目の方向に向かって」となります。

この様に方向は各状況によって指定出来ます。

今回行を取得する際は(xlUp）を採用しているという事です。

コードの最後です。.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列方向にデータを探す中で最初に入力があるセルの列番号をだけを教えてください」

という考え方です。

この考えであればリストの外側からリストの範囲を決める事ができます。

途中に空欄が居ても画面から見えてなくても行列の最終地点を認識することが出来ます。

最後に行と列の値を合わせて考える事でリストの最終地点を認識する事にします。

最終行が15(15行目)と最終列が6(F列)という事が分かったのでCellsに当てはめてみましょう。

これで「セルF15がリストの最終地点」とVBAに認識してもらう事が出来たというわけです。

ちょっと待ってください。よく見るとCellsの行と列は数値だったはずですが漢字が入ってますね。

これは変数という機能です。

この機能を使えば「都度データ範囲が違ったり飛び地がある様なデータにも対応できるコード」ができます。

次回の記事で紹介していきます。

「都度データ範囲が違ったり飛び地がある様なデータにも対応できるコード」の一例を紹介しました。

不確定な値を掴むという案件をリストの最終地点を認識するという事例で再現した次第です。

上記のような例ではなくても何をするにも始点と終点が必要です。

VBAに作業範囲を認識してもらわないとどんなコードを書けば良いのか定まりません。

VBAに作業してもらうには「○○から△△まで」と教えてあげる必要があります。

つまりVBAに限らずプログラミングでは都度「作業範囲を決める」という仕事があると思ってください。

次は実際にコードを書いてみる事にしましょう。

変数という機能の解説を積み残しているので次回記事に進んでください。

変数は不思議な機能です。難しいかもしれませんが覚えると世界が広がりますよ。