最終行の取得
ExcelVBAでは、データの最終行を取得するというシチュエーションが非常に多い。
通常データは、一行目に項目名を置き、１行１データという形で配置する場合が殆どだ。実はこの形が最もデータを取り扱いしやすく、検索、集計、加工もしやすいので重宝する。
このような形のデータを巡回するには最終行を取得する必要がある。
ExcelVBAでは、最終行を取得するための定番の書き方があるので紹介しよう。
最終行を取得するコード
以下は前回使ったデータだ。現在は最終行が10行目になるが、今後データが追加されたり削除されたりすると最終行は変化する。
プログラミングで最終行を取得できれば、汎用的に動作させる事ができる。
プログラムコードは以下のようになる。
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row プログラムコードが少々長くなるので、変数に入れて使うのが便利だろう。
実は当サイトの「備忘録」というページに、ExcelVBAで定番的に使うプログラムコードを掲載している。
その一番目が、上記のコードになる。（正直、よく使うが覚える事がなかなかできないコードだ。）
プログラムコード解説
このコードの特徴は、途中で空白を挟んでも指定した列の最終行を取得できることろだ。
詳しい仕組みが知りたい人は、以下のページを参照してもらいたい。
・最終行を取得
Sub testMacro()
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print lastRow
End Sub とりあえず意味がわからなくても使う事ができるので、丸暗記してもコピペしてもOKだ。
気を付けてほしいのは、Cellsの第二引数だ。ここには最終行を取得する列を指定する。今回は 1 として 1列目であるA列を指定しているが、必要に応じて値を変更する必要がある。
さらに、ActiveSheet の部分は、現在開いているシートが対象なので、別のシートの最終行を取得したい場合は、シート指定が必要になる。（別のシートの扱いは、もう少し後で解説する。）
プログラミングでの使用例
前回の最後の部分で、データを抽出する前に、それまでの値（F列～I列）をクリアする必要があることを学んだ。
この時、適当に大きめの行数を指定してセルの値をクリアしていたが、今回は最終行を指定してクリアする範囲を特定する事にしよう。
また、A列～D列部分のデータも増減する事を考慮して最終行を求めるようにする。
Sub macro6()
Dim i As Long
Dim lastRow As Long, row As Long
'F列の最終行を取得 (1)
lastRow = Cells(Rows.Count, "F").End(xlUp).row
If lastRow < 2 Then lastRow = 2 '(2)
Range("F2:I" & lastRow).Clear '(3)
'A列の最終行を取得 (4)
lastRow = Cells(Rows.Count, "A").End(xlUp).row
row = 2
For i = 2 To lastRow '(5)
If Cells(i, "B") = "シャープペン" Then
Range("A" & i & ":D" & i).Copy
Cells(row, "F").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
row = row + 1
End If
Next i
End Subプログラム解説
最初にF列～I列に値があればクリアする必要がある。ここに何行のデータがあるか不明なので、F列で最終行を取得するようにする。(1) の部分がその部分だ。Cellsの第2引数を “F” を指定するのを忘れないようにしょう。
もしF列～I列に値が無かった場合、(1)部分で得られる値は １ となってしまう。ここでは２行目から値を表示させたいので、lastRow の値は２以上でないとうまくない。そのコードが (2) の部分だ。
F列の最終行が決まったので、(3)のコードで抽出データ範囲をクリアする。
今度はデータのある A列の最終行を取得する。(4) ここでは変数 lastRow を使い回して使用している。
最後は、データの抽出工程になる、For文の最終カウントに lastRow を用いて検索する対象を最終行に指定している。
実行すると上記のように、これまでの抽出結果をクリアしてから、新しい抽出結果を表示できるようになった。
空白までの最終行を取得
これまでの方法はでは、途中に空白行を挟んでも最終行を取得してくれた。
しかし場合によっては、空白行が来るまでの最終行を取得したいケースもある。この場合は、以下のような方法がある。
Sub getLastRow2()
Dim lastRow As Long
'lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
lastRow = ActiveSheet.Cells(1, "A").End(xlDown).row
Debug.Print lastRow '5
End Sub わかりやすいように前回の最終行取得コードはコメントアウトしておいた。
今度の空白行までの最終行では、Cellsにデータの開始位置を指定して、End(xlDown).row を使う。
列指定は同じだ。
ActiveSheet.Cells(1, “A”).End(xlDown).row
シート指定.Cells(開始行 , 列指定).End(xlDown).row
結果は 5 となり５行目が空白行までの最終行となる。
自作のコードで最終行まで巡回
空白行までの最終行であれば、自作のコードで最終行までの巡回するプログラム書くのはそれほど難しくない。
Sub macro7()
Dim i As Long
For i = 2 To 1000 '最終回に適当に大きい数値を指定
If Cells(i, "A") = "" Then Exit For 'A列に空白が来たらForを抜ける
Debug.Print Cells(i, "B")
Next i
End Subこのプログラムでは、For文の最終回数に適当に大きな値（1000）を入れて巡回を開始するが、もしA列に空白が来た場合は、Exit For で巡回を抜けるので実際には 5行目で処理を抜ける事になる。
「適当に大きな値」という部分がプログラミングとしては怪しい感じがするが、仕組みをあれこれ考えるというのは大事な事だと思う。ActiveSheet.Cells(1, “A”).End(xlDown).row のようなコードが思い浮かばなかった場合にはこれでも十分対応できる。
ただ、空白行を挟んだ最終行を取得したい場合は、ActiveSheet.Cells(Rows.Count, “A”).End(xlUp).row を使う事になるだろう。
まとめ
プログラミングを学んでいると「おまじないのように覚える」という言い回しが使われる事がある。
内容をよく理解して使用する方が良いのだが、形だけを覚えて使う回すという方法もそれなりに利点はある。一度使ってみて「使えるな」と思える手法は、おまじない式に部品として使えるようにしておくと便利だ。
今回の最終行の取得をまとめると以下のようになる。
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
シート指定 . Cells( 最下行 , 列指定 ).End(xlUp).Row
指定するのは、列指定部分と、シート指定部分になる。あとは、おまじないのように使用すれば問題ない。
どこかに（テキストファイルなど）にコピーしておけば、必要な時にコピペで対応できる。
とにかく頻出なので使えるようにしておこう。
空白行が来るまでの最終行を取得したい場合では、ActiveSheet.Cells(1, “A”).End(xlDown).row を紹介した。
上記の最終行と書き方が似ているので、間違えないように注意しよう。
自作で空白行までを巡回するプログラムでは、「適当に大きな値」を使って巡回し、Exit For で抜けるアイデアを紹介した。実はこういうケースでは、Do While を使うのが定番となる。
ただ、Do While は使い方を間違うと無限ループとなる危険性があるので初心者にはお勧めしない。無限ループの対処はExcelファイルを強制終了させる必要があるので、慣れている人でも心的ダメージが大きい。
Do While は For文でも代用できるので当分の間は、For文でプログラミングするようにしよう。
コメント