最終レコードがExcelワークシート上の何行目になるのかを求めるサンプルコードです。VBA集計業務に幅広く使われる重要なスキルです。
では、下記のVBAコードを真似て書いてみてくださいね。
今回はたった3行のVBAコードですね。
なお、「Range("A" & Rows.Count).End(xlUp).Row」を入力する際に、気づかれたかもしれませんが、「Range("A"&Rows.Count)」のつぎの「.」を入力すると以下のように入力候補が表示されたかと思います。
この機能は、「.」直前のオブジェクトが取り得るプロパティ(属性)やメソッド(命令)が候補表示されるものです。
上図の状態で「Tab」キーを押せば、「End」まで一気に入力されますので、特に長いプロパティやメソッドを入力する際には助かります。
では、書き終わりましたら、さっそくVBAコードを実行してみてください。
今回のVBAコードは、レコードの最終行番号を取得するものです。実行後、確かに最終レコードがあるExcelシートの行番号16と、VBAの実行結果が一致しましたね。
では、つぎにコードについてお話ししましょう。
1 2 3 4 5 6 7 8 9 10 11 | Option Explicit 'レコードの最終行番号を取得する Sub Sample019() 'レコードの最終行番号を入れる長整数型変数 Dim lngERow As Long 'レコード最終行を変数lngERowへ代入 lngERow = Range("A" & Rows.Count).End(xlUp).Row End Sub |
6行目:Dim lngERow as long
取得したレコードの最終行番号を入れるための長整数型の変数を定義しています。
9行目:lngERow = Range("A" & Rows.Count).End(xlup).Row
VBA集計業務の頻出スキルの1つになりますので、ここからゆっくりとお話ししたいとおもいます。
まず左辺の「lngERow」は、6行目で定義したレコードの最終行番号を入れるための変数ですね。よって、右辺の長い式によって、最終レコードの行番号が求められているのが分かります。
さて、難解な右辺については下図でゆっくり説明したいと思います。まずは、前回のオブジェクト変数の記事でもお話ししました。単体セルの表示方法を下図に示しますね。
Rangeオブジェクトの1つの表記方法ですが、"(ダブルクォーテーション)で囲ったセル番地を、Range()のカッコ内に入れる方法です。
この表記方法を確認した上で、次の図に表した今回のRangeオブジェクトを見てみましょう。
これより、「Range("A" & "1048576")」ということになりますので、もう少し整理すると「Range("A1048576")」となりますね。ということは、このセルというのは、以下の図に示すようにA列の最終セルを指すことになります。
よって、ここまでの理解により、下記のように「Range("A"&.Rows.Count)」の部分は、「Range("A1048576")」に置き換えられますよね。その上で、後半の「.End(xlUp).Row」のお話しをすすめますね。
「.End(xlUp)」は、図中の文字だけでは理解しずらいと思いますので、またまた下図を用意しました。
さて、ここで黄色いセルがありますね。これらのうち、例えば「B12」セルを選択状態にした後、「Ctrl」+「↑」を押してみてください。
「B2」へジャンプしましたよね。これは、VBAコード上では、「Range("B12").End(xlUp)」と同じことなんです。
同じように、「C12」、「D12」も確認してもらうと分かるように、出発点のセル(この例では黄色いセル)から、途中空白セルを飛ばして次の「空白ではないセル」までジャンプしてますよね。
そうなると、今回のコード「Range("A" & Rows.Count).End(Xlup)」(=Range("A1048576").End(xlUp))の部分は以下のように、Range("A16")にジャンプしたのと同じと考えられますよね。
どうでしょう。ここまで大丈夫でしょうか。ここまで来れば、最後の「.Row」は簡単ですよ。
「.Row」は、行番号のことですからジャンプ先の「Range("A16")」の行番号、すなわち「16」になります。今回のコードの実行結果の「16」と一致しましたよね。
あとは、求められた最終行番号をMsgBox関数で表示するだけです。
まとめ
さて、今回はVBA集計業務では非常によく使う、レコードの最終行の求め方についてお話ししました。今後のVBAコードの中でも、今回のバリエーションが頻繁に出現しますが、基本的な考え方は同じです。
また、今回のバリエーションが出た際には、その都度詳しくお話ししたいと思いますので、安心してくださいね。