第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)
エクセルの表は、横の項目はあまり変更はないものですが、
縦のデータ数、つまり行数は常に変わるものです。
データ数に応じて、最終行まで繰り返す方法が必要です。
それにはデータの最終行が分かれば良いのです。
幸いなことに、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行番号という事です。
11
これを取得できれば良い訳です。
この11を取得するにはどうしたらよいでしょうか。
数万行あったら、スクロールも面倒です。
そういう時は、
A列の適当なセル(A1セルで良い)を選択して、
Ctrl + ↓
とすれば、連続データの最終行に飛んでいきます。
上記の表なら、
A1セルで、
Ctrl + ↓
A11セルに選択セルが移動します。
これなら、データが何万行あっても、一瞬で最終行に移動できます。
これをマクロVBAで書くと、
ですが、A列の途中に空白セルがあると、そこで止まってしまいます。
Ctrl + ↓
これを何度も押して、一番下に移動した経験がある方も多いのではないでしょうか。
つまり、上のコードでは、ちゃんとした最終行を取得できません。
また、もし1行目の項目名しかないと、
Ctrl + ↓
これでは、シートの一番下の、1048576行まで行ってしまいます。
シートの一番下の行から、
Ctrl + ↑
これで、データの入っている最終行で止まります。
つまり、
A1048576のセルから、
Ctrl + ↑
これで間違いなく、データの最終行に移動できます。
これをマクロVBAで書くと、
Rows.Countは、
1048576
という数値と同じ意味です。
シートの一番下の行の数値、つまり、シートの全行数を取得しています。
ですので、
Cells(1048576, 1).End(xlUp)
これと同じことになります。
.End(xlUp)
これらが、
Ctrl + ↓やCtrl + ↑
これらを意味します。
しかし、これはあくまで最終行のセルです、箱としてのセルそのものです。
行数ではありません。
セルの行数は、
.Row
と付け足すと、ぞのセルの行数が取得できます。
つまり、
A1048576のセルから ・・・ Cells(Rows.Count, 1)
Ctrl + ↑で移動したセル ・・・ .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
これに取り替えることで、
(デー件数が変わっても)常に最終行まで処理されるようになります。
これを変更して、
このようにすることで、常に最終行まで処理されます。
実際に、データを追加したり、削除して、
ちゃんと最終行まで計算されることを確認してください。
この終了値の
11
を
Cells(Rows.Count, 1).End(xlUp).Row
これに取り替えることで、
(デー件数が変わっても)常に最終行まで処理されるようになります。
実際に、データを追加したり、削除して、
ちゃんと最終行まで計算されることを確認してください。
VBAの文法を覚える必要があるか
上記の最終行取得のVBAコードを説明しようとすると、かなり難しい文法説明が必要になります。
VBAを書くだけなら、上記のコードをコピペすれば済むことです。
オブジェクト、プロパティ、メソッド
ここを訪れるまでに、書籍やネットでVBAを勉強したことある人は、これらの言葉で挫折した人も多いのではないでしょうか。
オブジェクトとは
VBAを書くだけなら、上記のコードをコピペすれば済むことです。
ここを訪れるまでに、書籍やネットでVBAを勉強したことある人は、これらの言葉で挫折した人も多いのではないでしょうか。
対象物です。
ほとんどのオブジェクトは、マウスで操作できるもので、そのマウスで操作する対象の事です。
エクセルの代表的なオブジェクトと言えば、ブック、シート、セルという事になります。
ほとんどのオブジェクトは、マウスで操作できるもので、そのマウスで操作する対象の事です。
エクセルの代表的なオブジェクトと言えば、ブック、シート、セルという事になります。
プロパティとは
オブジェクトの属性・状態・様子といったものになります。
オブジェクトを物体としてとらえると、その物体の状態を表すという事です。
オブジェクトを物体としてとらえると、その物体の状態を表すという事です。
メソッドとは
オブジェクトに動作を与える命令です。
オブジェクトに対して、こう動作しろ、こうなれ、と命令するという事です
オブジェクトに対して、こう動作しろ、こうなれ、と命令するという事です
コレクションとは
同一のオブジェクトの集まりです。
多くの場合、オブジェクト名称を複数形にした名前になっています。
多くの場合、オブジェクト名称を複数形にした名前になっています。
上記の最終行取得のコードを、これらの用語を使って説明しても簡単に理解できるものではありません。
そのような説明を聞いて理解できるなら苦労しないし、ここには来ていないはずです。
こういう事をちゃんと覚えるのは後回しにしましょう。
今の段階では、用語としてそういう言い方をすると、それだけの理解で十分です。
マクロを書くのに、このようなオブジェクト、プロパティ、メソッドが何で、どういうものかなどという事は、知らなくても書けます。
しかし、書籍やネットを見れば、このような用語が出てきますし、
エクセルも、書き間違えてエラーを起こすと、これらの言葉で注意します。
その時に、何の事かが分からないと困ってしまいます。
逆に言えば、そのような時に困らなければ、それで十分だという事です。
本当に詳しい話、本質を語れば、
そもそもオブジェクト、プロパティ、メソッドの違いなんて、ほとんど大した違いはありません。
VBAでの使い方・書き方の違い程度の事でしかないのです。
オブジェクトは、階層構造になっていて、オブジェクトの中にオブジェクトがある。
(あらゆる物体は、より小さな物体の集まりです)
オブジェクトは、プロパティとメソッドの集合体である。
(どういう物体かをプロパティが決めていて、メソッドでその物体は動かせる)
このようにイメージしてください。
これらの用語については、後の回にもう少し詳しく解説します。
そのような説明を聞いて理解できるなら苦労しないし、ここには来ていないはずです。
こういう事をちゃんと覚えるのは後回しにしましょう。
今の段階では、用語としてそういう言い方をすると、それだけの理解で十分です。
しかし、書籍やネットを見れば、このような用語が出てきますし、
エクセルも、書き間違えてエラーを起こすと、これらの言葉で注意します。
その時に、何の事かが分からないと困ってしまいます。
逆に言えば、そのような時に困らなければ、それで十分だという事です。
そもそもオブジェクト、プロパティ、メソッドの違いなんて、ほとんど大した違いはありません。
VBAでの使い方・書き方の違い程度の事でしかないのです。
(あらゆる物体は、より小さな物体の集まりです)
オブジェクトは、プロパティとメソッドの集合体である。
(どういう物体かをプロパティが決めていて、メソッドでその物体は動かせる)
このようにイメージしてください。
ExcelマクロVBA入門等の対応ページ
第18回.最終行の取得(End,Rows.Count)
オブジェクト式について
・エクセルVBAにおける最終行取得の必要性 ・.End(xlDown):Ctrl+↓ ・.End(xlUp):Ctrl+↑ ・Endプロパティの方向(↑↓←→)について ・セルの行数を取得するRowプロパティ ・Cells(Rows.Count, 1).End(xlUp).Rowを日本語に訳す ・EndプロパティがRangeオブジェクトを返す ・Endプロパティの問題点 ・最終行に関するサイト内のページ
オブジェクト式について
・オブジェクト式の書き方 ・オブジェクトは直接指定できない ・オブジェクト式の書き始めは何か ・メソッドとプロパティの違いについて ・オブジェクト式の最後に
同じテーマ「マクロVBA再入門」の記事
・セルに文字を入れる ・セルに数字を入れる ・マクロVBAを実行 ・RangeとCells ・セル範囲(複数セル)に一括入力 ・「セルに数字や文字を入れる」のまとめ ・ExcelマクロVBA入門の対応ページ
第4回.セルの値を使って計算する(四則演算)・セルの値を使って足し算 ・セルの値を使って四則演算 ・セルの値を使った応用計算 ・ワークシートの関数を使った計算 ・ExcelマクロVBA入門の対応ページ
第5回.同じ計算を行数分繰り返す(For~Next)・練習用のデータ作成について ・1行だけの計算をする場合は ・VBAの変数とは ・変数を使って行をずらしていく ・デバッグ:ステップ イン F8 ・変数を使った繰り返し処理 ・For~Nextの補足説明 ・VBAコードの書き方について ・ExcelマクロVBA入門の対応ページ
第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)第7回.セルの値によって計算を変える(Ifステートメント)
・Ifの説明用のサンプル ・マクロVBAでのIfの使い方 ・Ifステートメントの書き方 ・Ifのネスト(入れ子) ・AndとOr ・ElseIfとSelect Caseについて ・マクロVBAは、プログラミングです ・Ifステートメントの練習問題 ・改行方法について ・ExcelマクロVBA入門等の対応ページ
第8回.表範囲をまとめて消去する(OffsetとClearContents)・見出しを残しデータ部分のみ消去 ・Offsetを使って範囲をずらす ・データ範囲に関係なく消去する場合 ・ExcelマクロVBA入門等の対応ページ
第9回.関数という便利な道具(VBA関数)・これだけは覚えておきたい必須VBA関数 ・VBA関数の練習 ・ExcelマクロVBA入門の対応ページ
第10回.ワークシートの関数を使う(WorksheetFunction)・マクロVBAでのワークシート関数の使い方 ・長いスペルの単語を簡単に入れる方法 ・ワークシート関数を使ってみよう ・ワークシート関数を使う時の注意点、エラー対策 ・ExcelマクロVBA入門等の対応ページ
第11回.分からない事はエクセルに聞く(マクロの記録)・マクロの記録の使い方 ・実際に分からない事を調べる時の手順 ・「分からない事はエクセルに聞く」のまとめ ・ExcelマクロVBA入門等の対応ページ
第12回.エクセルの言葉を理解する(オブジェクト、プロパティ、メソッド)・VBAが表示するメッセージ ・オブジェクト、プロパティ、メソッド ・プロパティとメソッドの違い ・オブジェクトの階層(親子構造) ・プロパティの省略 ・エクセルの言葉であるVBAを日本語に翻訳する ・最終行取得のコードを訳してみる ・VBA用語を覚えることについて ・ExcelマクロVBA入門等の対応ページ
第13回.セルのコピペ方法を知る(CopyとPaste、さらに)・セルをコピーして貼り付け ・セルをコピーして値貼り付け ・セルをコピーして書式貼り付け ・PasteSpecial(値貼り付け、書式貼り付け等)の細かい指定 ・もっと簡単なコピペ方法はないものか ・もっと効率的な「セルをコピーして貼り付け」 ・マクロならではの「値のコピー」 ・「セルのコピペ方法を知る」のまとめ ・ExcelマクロVBA入門等の対応ページ
新着記事NEW ・・・新着記事一覧を見る
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.RangeとCellsの使い方|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.並べ替え(Sort)|VBA入門
10.メッセージボックス(MsgBox関数)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。