VBA再入門
第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日:2020-09-26

第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)


エクセルの表は、横の項目はあまり変更はないものですが、
縦のデータ数、つまり行数は常に変わるものです。


前回のように、固定の回数しか繰り返せないのでは困ります。
データ数に応じて、最終行まで繰り返す方法が必要です。
それにはデータの最終行が分かれば良いのです。
幸いなことに、VBAにはデータの入っている最終行を取得する方法が用意されています。

前回と同じ、以下の表を使います。

Excel マクロVBA 実行結果


表の最終行を取得する

上記の表なら、データの最終行である、
11
これを取得できれば良い訳です。
この11を取得するにはどうしたらよいでしょうか。

数十行、数百行程度なら、スクロールして最終行を目視で確認できますが、
数万行あったら、スクロールも面倒です。
そういう時は、
A列の適当なセル(A1セルで良い)を選択して、
Ctrl + ↓
とすれば、連続データの最終行に飛んでいきます。
上記の表なら、
A1セルで、
Ctrl + ↓
A11セルに選択セルが移動します。
これなら、データが何万行あっても、一瞬で最終行に移動できます。
これをマクロVBAで書くと、

Cells(1, 1).End(xlDown)

このように書きます。
ですが、A列の途中に空白セルがあると、そこで止まってしまいます。
Ctrl + ↓
これを何度も押して、一番下に移動した経験がある方も多いのではないでしょうか。
つまり、上のコードでは、ちゃんとした最終行を取得できません。
また、もし1行目の項目名しかないと、

Ctrl + ↓
これでは、シートの一番下の、1048576行まで行ってしまいます。

ではどうしたら良いか・・・
シートの一番下の行から、
Ctrl + ↑
これで、データの入っている最終行で止まります。
つまり、
A1048576のセルから、
Ctrl + ↑
これで間違いなく、データの最終行に移動できます。
これをマクロVBAで書くと、

Cells(Rows.Count, 1).End(xlUp)

このように書きます。
Rows.Countは、
1048576
という数値と同じ意味です。
シートの一番下の行の数値、つまり、シートの全行数を取得しています。
ですので、
Cells(1048576, 1).End(xlUp)
これと同じことになります。

.End(xlDown)
.End(xlUp)
これらが、
Ctrl + ↓Ctrl + ↑
これらを意味します。

これで最終行のセルは分かりました。
しかし、これはあくまで最終行のセルです、箱としてのセルそのものです。
行数ではありません。
セルの行数は、
.Row
と付け足すと、ぞのセルの行数が取得できます。
つまり、
A1048576のセルから ・・・ Cells(Rows.Count, 1)
Ctrl + ↑で移動したセル ・・・ .End(xlUp)
行数 ・・・ .Row
という事です。

Cells(Rows.Count, 1).End(xlUp).Row

このように書くと、最終行の行数になります。
最初の画像の表なら、

Cells(Rows.Count, 1).End(xlUp).Row
これは、
11
にまります。

つまり、

Cells(Rows.Count, 1)
これは、
A1048576セルの事で
そこから、
.End(xlUp)
そのセルからCtrl + ↑で移動したセルの.Row行番号という事です。

表の最終行まで繰り返す

そこで、前回の繰り返し処理の

For i = 2 To 11
この終了値の
11

Cells(Rows.Count, 1).End(xlUp).Row
これに取り替えることで、
(デー件数が変わっても)常に最終行まで処理されるようになります。

Excel マクロVBA サンプルコード

これを変更して、

マクロ VBA サンプル画像

このようにすることで、常に最終行まで処理されます。
実際に、データを追加したり、削除して、
ちゃんと最終行まで計算されることを確認してください。

VBAの文法を覚える必要があるか

上記の最終行取得のVBAコードを説明しようとすると、かなり難しい文法説明が必要になります。
VBAを書くだけなら、上記のコードをコピペすれば済むことです。

オブジェクト、プロパティ、メソッド
ここを訪れるまでに、書籍やネットでVBAを勉強したことある人は、これらの言葉で挫折した人も多いのではないでしょうか。

オブジェクトとは
対象物です。
ほとんどのオブジェクトは、マウスで操作できるもので、そのマウスで操作する対象の事です。
エクセルの代表的なオブジェクトと言えば、ブック、シート、セルという事になります。
プロパティとは
オブジェクトの属性・状態・様子といったものになります。
オブジェクトを物体としてとらえると、その物体の状態を表すという事です。
メソッドとは
オブジェクトに動作を与える命令です。
オブジェクトに対して、こう動作しろ、こうなれ、と命令するという事です
コレクションとは
同一のオブジェクトの集まりです。
多くの場合、オブジェクト名称を複数形にした名前になっています。
上記の最終行取得のコードを、これらの用語を使って説明しても簡単に理解できるものではありません。
そのような説明を聞いて理解できるなら苦労しないし、ここには来ていないはずです。
こういう事をちゃんと覚えるのは後回しにしましょう。
今の段階では、用語としてそういう言い方をすると、それだけの理解で十分です。

マクロを書くのに、このようなオブジェクト、プロパティ、メソッドが何で、どういうものかなどという事は、知らなくても書けます。
しかし、書籍やネットを見れば、このような用語が出てきますし、
エクセルも、書き間違えてエラーを起こすと、これらの言葉で注意します。
その時に、何の事かが分からないと困ってしまいます。
逆に言えば、そのような時に困らなければ、それで十分だという事です。

本当に詳しい話、本質を語れば、
そもそもオブジェクト、プロパティ、メソッドの違いなんて、ほとんど大した違いはありません。
VBAでの使い方・書き方の違い程度の事でしかないのです。

オブジェクトは、階層構造になっていて、オブジェクトの中にオブジェクトがある。
(あらゆる物体は、より小さな物体の集まりです)
オブジェクトは、プロパティとメソッドの集合体である。
(どういう物体かをプロパティが決めていて、メソッドでその物体は動かせる)
このようにイメージしてください。

これらの用語については、後の回にもう少し詳しく解説します。

ExcelマクロVBA入門等の対応ページ

第18回.最終行の取得(End,Rows.Count)
Excelワークシートにおける表の最終行の取得は、VBAの必須技術になります、エクセルVBAにおける最終行取得の必要性 エクセルは表計算ソフトです、つまり縦横の表を扱います、データは横に項目があり、縦に項目に対するデータが入っている事が一般的です。しかし、そのデータ行数は決まった行数ではない事が普通です。

オブジェクト式について
オブジェクトは、objectであり、物・物体のことです、VBAで何か操作をしようとする対象となるものがオブシェクトです、オブジェクト式とは、オブジェクトを操作しようとしたときに、オブジェクトを特定するための書き方です。ただし「オブジェクト式」という言い方を特別なものとして覚える必要はありません。



同じテーマ「マクロVBA再入門」の記事

第3回.セルに数字や文字を入れる(RnageとCells)
第4回.セルの値を使って計算する(四則演算)
第5回.同じ計算を行数分繰り返す(For~Next)
第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)
第7回.セルの値によって計算を変える(Ifステートメント)
第8回.表範囲をまとめて消去する(OffsetとClearContents)
第9回.関数という便利な道具(VBA関数)
第10回.ワークシートの関数を使う(WorksheetFunction)
第11回.分からない事はエクセルに聞く(マクロの記録)
第12回.エクセルの言葉を理解する(オブジェクト、プロパティ、メソッド)
第13回.セルのコピペ方法を知る(CopyとPaste、さらに)


新着記事NEW ・・・新着記事一覧を見る

VBA100本ノック 37本目:グラフの色設定|VBA練習問題(12月1日)
VBA100本ノック 36本目:列の並べ替え|VBA練習問題(12月1日)
VBA100本ノック 35本目:条件付き書式|VBA練習問題(11月29日)
VBA100本ノック 34本目:配列の左右回転|VBA練習問題(11月28日)
VBA100本ノック 33本目:マクロ記録の改修|VBA練習問題(11月26日)
VBA100本ノック 32本目:Excel終了とテキストファイル出力|VBA練習問題(11月25日)
VBA100本ノック 31本目:入力規則|VBA練習問題(11月24日)
将棋とプログラミングについて~そこには型がある~|エクセル雑感(11月22日)
VBA100本ノック 30本目:名札作成(段組み)|VBA練習問題(11月22日)
VBA100本ノック 29本目:画像の挿入|VBA練習問題(11月21日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ