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

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

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

前回はRangeオブジェクトを使いオブジェクトとプロパティの関係について説明しました。

VBAを勉強するうえで必ず通らないといけない道です。嫌にならない様に少しずつ出していきます。

仕事をする範囲が定まらない時にどうしようか？というところで前回記事の話が終わっていましたね。

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

マクロでも自力で探しに行って決めてしまえば良いのでは？と思いませんか。検証してみましょう。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

本来は画面からは見えないセルを取得する為に作業をするのですが見易くする為小さいリストにしています。

20行×10列前後であれば小さいサイズのPCでも一画面で全部を見れると判断しこの大きさにしました。

リストの列と行を数える際の考え方は前項で書いた方法と同じです。（A列で行、5行目で列を確認します）

分かりやすい様にセルに色を付けておきました。最終行は青、最終列は黄色の終端を数える事になります。

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

以降で具体的にどうやって列と行の番号を取得するのかを説明します。

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

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

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

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

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

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

多分今？が出ていると思います。

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

まずは「CellsプロパティからRangeオブジェクトを指定できる」というぐらいに考えておいてください。

勉強を進めていくと分かってきます。

いきなり現れたCellsプロパティですがここで登場したのには理由があります。

以降でCellsプロパティを使ってRangeオブジェクトを指定しないといけなくなります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Cellsプロパティを使った最終行と最終列を取得するコードは数値が取得できます。

その数値をCellsプロパティの引数にするとコード内で都度異なるセルを掴む事が出来る様になります。

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

その値を範囲を指定するコードに展開します。これで最終行と最終列が異なるリストに対応出来ます。

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

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

今回は値が確定している数値から見ていきます。

行のコードの中に居る1は表の１列目(A列)を表現しています。

上の画像で見るとセルA5と青いゾーンが含まれたA列ということになります。

列のコードの中に居る5は表の見出し行である5行目を見ています。

上の画像で見るとセルA5と黄色ゾーンが含まれた5行目ということになります。

まずはコードの前半分を考えます　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は「上方向」です。

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

Range(“A1048576”)からxlUpで1行目方向に値を探しに行く事で結果表の一番外側の値を取得できます。

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

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

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

コードの最後です。.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に当てはめてみましょう。

Cells(行,列)　リストの最終地点を表すコードになります。

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

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

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

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

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

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

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

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

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

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

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

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

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