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

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

マクロを使うとデータの最終行と最終列を認識出来る様になる

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

EnjoyExcel
EnjoyExcel

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

データの最終行と最終列が認識できる様になると良いことがたくさんあります。

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

前回の記事

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

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

本記事での課題

今回の課題です。

EnjoyExcel
EnjoyExcel
課題

表(リスト)の最終行と最終列を取得出来る様にする

初心者向けエクセルマクロ学習シリーズ

列と行について

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

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

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

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

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

改めて列と行の確認です

まず知識を整理します。そもそも列と行の理解って大丈夫ですよね?

エクセルの列と行

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

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

事例:最終行と最終列を取得する

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

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

リストの列と行を捉える

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

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

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

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

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

最終列と最終行が1画面では見えないデータ
エクセルのシートです。最終の列、行はこの状態では見えないので分かりません。

列と行を数える場所を決める

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

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

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

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

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

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

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

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

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

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

最終列と最終行を取得するためのコードを考える

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

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

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

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

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

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

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

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

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

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

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

セルF15が空欄だったとしてもリストの最終地点を取得する事ができますよ

コード

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

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

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

コードを紹介します
  • 列 = Cells(5, Columns.Count).End(xlToLeft).Column
  • 行 = Cells(Rows.Count, 1).End(xlUp).Row

ここでポイントを1つ提示します。後程回収しますので少しだけ覚えておいて下さい。

このコードから得られるのは列番号(数値)と行番行(数値)です

Cellsプロパティ

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

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

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

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

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

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

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

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

今からはRangeオブジェクトとCellsプロパティについて勉強していきます。

Cellsプロパティは行が先

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

これはCellsプロパティを使ってコードを書く場合の行、列の順番を意識して記事を書いた為です。

ポイントです
  • Cellsプロパティは Cells(行,列) という建付けになっています
  • CellsプロパティはRange(”*”)の形を返します

Cellsプロパティを使う時の行と列の並びはRangeオブジェクトの時とは逆になります。

順番さえ間違えなければCellsプロパティで書いたコードも結果としてはRangeオブジェクトの値を返す事が出来ます。

Cellsプロパティでもセルを指定する事ができます

ではなぜCellsプロパティが出てきたのか?今回はなぜRangeオブジェクトを使わないのか?と思いませんか。

これには理由があります。

RangeオブジェクトとCellsプロパティは使い勝手で選ぶ

RangeオブジェクトとCellsプロパティはそれぞれ適した使い時があります。

以下の様な使い分けをすると思ってください。

ポイントです
  • 毎回扱うセルの値が確定している時は Rangeオブジェクトを使う(勝手が良い)
  • 毎回セルの値が確定していない時は Cellsプロパティを使う(勝手が良い)

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

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

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

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

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

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

Cellsプロパティの引数は数値

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

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

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

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

引数が数値だとさらにメリットがあります。繰り返し処理や条件分岐など他の機能との相性も良くなります。

RangeオブジェクトとCellsプロパティは引数内の値が異なります
  • Range(“A1”) ・・・ かっこ内の引数は文字列です
  • Cells(1,1) ・・・ かっこ内の引数は数値です

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

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

このコードから得られるのは行番行(数値)と列番号(数値)です

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

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

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

Cellsプロパティの引数が数値だと何ができるのか

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

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

行、列を取得するコードと範囲を指定するコードとの関係を示しています。

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

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

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

Cellsプロパティに数値を使う事でコード実行時に取得した行と列の数値をセル範囲の終点に展開できる事が分かります。

建付けは説明出来ました。実際のコードを見ていきましょう。

コードが表現していること

文章を理解し易い様に再度画像とコードを提示します。

青の列で最終行の行番号を取得し黄色の行で最終列の列番号を取得します。
再度最終行と最終列を取得する為のコードを紹介
  • 行 = Cells(Rows.Count, 1).End(xlUp).Row
  • 列 = Cells(5, Columns.Count).End(xlToLeft).Column
コード内の数値

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

行のコードの中に居る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行目が最後の行なので以下の様な値が返ってきます。

エクセルの行は1048576行

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

という考え方です。

そうやって得られた値というのは「結果最終列だよね」という事になります。

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

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

予告:取得した行と列を効率よく使うには変数が必要

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

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

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

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

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

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

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

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

まとめ

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

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

本日勉強したこと
  1. RangeオブジェクトとCellsプロパティの関係
  2. Endプロパティの使い方
  3. 画面から見えないセルも指定できる
考え方は分かりましたか

Enjoy Excel

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

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

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

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

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

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

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

参考:おすすめ書籍

初心者様向けの書籍です。多くの書籍とは一風変わった事を書いてますが共感が持てたので紹介します。

EnjoyExcel
EnjoyExcel

「マクロを勉強するのは少しでも仕事を効率化させる為」であり

「マクロを完璧にマスターするわけではない」という考えのもと書かれた本です。

タイトルとURLをコピーしました