Office TANAKA - Excel VBA Tips[もう最終行の取得にEndを使わない]

たとえば、件数が分からないデータを順番に処理するようなとき、For Nextに指定する"最終行"を、Endで調べるのが一般的でした。たとえば、次のような書き方が多いですね。

Sub Macro1()
    Dim i As Long, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To LastRow
        Cells(i, 2) = Left(Cells(i, 1), 1)
    Next i
End Sub

この「Cells(Rows.Count, 1).End(xlUp).Row」部分について、"意味が分からない"という方が多いです。それどころか、これを正しく解説しているコンテンツや動画なども、見た記憶がありません。ヒドイのになると「これは理解しないでいいです。とにかく暗記してください」みたいな、マクロを語るのは100年早いレベルの教え方をしていたり、「このコードをメモ帳に書いて、デスクトップに置いておきましょう」みたいな、まるで幼稚園児レベルの発言も耳にします。本当に、本当に、お願いします。分からないなら黙っててください。そうでないと、マクロやVBAを学習しようとしている真摯な方々にとって、まさに"百害あって一利なし"となります。ええと...この件に関しては、根本的な部分で言いたいことが山ほどありまして。つい熱くなってしまいました。すみません、ちょっとコーヒー買ってきます。

さてさて、そんな、ほとんどの方が理解していない「Cells(Rows.Count, 1).End(xlUp).Row」ですけど、それでも使うしかありませんでした。なぜなら、実務のデータは「件数が分からない」から。まぁ、他のやり方も、あるっちゃありますが、この"End"を使うのが一般的でした。今までは。でも、これからは違います。Excelに、新しいやり方が追加されました。それが"トリム参照"です。これから詳しく解説しますが、この"トリム参照"が使えるのは、ワークシートのセル内でTRIMRANGE関数が使えるバージョンです。よく分からない方は、どこかのセルに「=TRIM」と入力して、関数オートコンプリートのリストに"TRIMRANGE"が表示されるか確認してください。表示されていればOKです。ちなみに、Excel 2019とかExcel 2022など、Excel 20XXという"永続ライセンス版"では使えないと思います。クラウドサービス「Microsoft 365」からインストールしたExcelで、徐々に使えるようバージョンアップしているようです。なお、TRIMRANGE関数や"トリム参照"について、詳しいことは下記のページをご覧ください。

TRIMRANGE関数の解説

トリム参照とは、簡単にいうと「範囲の外側にある空欄セルを除去する」参照方法です。今までの"列全体"を指定する方法と比べてみましょう。

【列全体の参照】

【トリム参照】

上図ではA列全体を参照しています。見慣れた「A:A」という書き方です。見て分かるように、7行目から1,048,576行も参照されています。一方のトリム参照では6行目までしか参照されていません。7行目から下は空欄セルなので除去されました。参照演算子に指定している「:.」がトリム参照です。VBAのCurrentRegionに似たイメージですね。さて、ここで重要なことは、この新しい参照演算子である「.」は、VBAでも使用可能だということです。

Sub Macro2()
    Range("A:.A").Select
End Sub

どうです？ちょっとワクワクしてきませんか。もう、意味不明なEndを使って、データ末尾の最終行を取得しなくていいんです。じゃ、このトリム参照を使って、For Nextに指定する最終行を取得してみましょう。まずは、ベタなやり方から。

Sub Macro3()
    Dim i As Long, LastRow As Long
    LastRow = Range("A:.A").Rows.Count
    For i = 2 To LastRow
        Cells(i, 2) = Left(Cells(i, 1), 1)
    Next i
End Sub

細かい話ですけど、プログラミングでは「自分がやりたいことをコードに表す」という発想が大事です。たとえば「(データが入力されている)最終行を取得したい」とき、A列の一番下のセル(Cells(Rows.Count,1))から、Endモード(End)で、上に向かってジャンプ(xlUp)して、どこだか分からないけど行き着くであろうセルの行番号(Row)を取得するって、ちょっと遠回りな動作ではありませんか？結果的にそれで判明するというだけで、本来やりたいのは「データが入力されている末尾の行番号」を調べたいんです。だったら、トリム参照を使う方が、やりたいことを表現しています。

「どっちでも、似たようなもんじゃね？」って感じますか？安心してください。話はここからです。そもそも、なぜ最終行を取得するのでしょう。それは、データが入力されている範囲をFor Nextで処理したいからです。でも待ってください。Range("A:.A")で、データが入力されているセル範囲を特定できるんです。だったら、For Nextではなく、For Eachを使う方が簡単でしょう。

Sub Macro4()
    Dim C As Range
    For Each C In Range("A:.A")
        ''Cに対する処理
    Next C
End Sub

ただし、ここでひとつ問題があります。先のマクロを実行してみましょう。

Sub Macro4()
    Dim C As Range
    For Each C In Range("A:.A")
        C.Offset(0, 1) = Left(C, 1)
    Next C
End Sub

1行目も処理されてしまいました。まぁ、そりゃそうですよね。ここでは、Range("A:.A")から1行目を除外したいです。これ、いろんな方法が考えられます。条件分岐のIfを使って「もし、1行目だったら～」と処理を分岐することもできますし、何ならResizeを使って1行目を除外する手もあります。ただ、そうなると、ちょっと面倒くさいです。せっかくのトリム参照ですが、魅力も半減します。でも「1行目を除去する」って、実はワークシート関数なら簡単です。使うのはDROP関数です。

今のExcelには、こんな便利なワークシート関数がたくさんあります。DROP関数についての詳しい解説は、下記のページをご覧ください。

TAKE関数/DROP関数の解説

このDROP関数を使えば超簡単です。とはいえ、ここでもひとつ問題があります。DROP関数は、WorksheetFunctionでは呼び出せません。なので、Evaluteを使います。Evaluteの詳しい解説は、下記のページをご覧ください。

Evaluateメソッド

Sub Macro4()
    Dim C As Range
    For Each C In [DROP(A:.A,1)]
        C.Offset(0, 1) = Left(C, 1)
    Next C
End Sub

だいぶスッキリしました。さらに、このトリム参照＋ワークシート関数を使うと、データの末尾に、新しいデータを入力するのも簡単です。ちなみに、Endを使うレガシーなコードと、トリム参照を使ったコードの両方をお見せします。

Sub Macro5()
''End
    Dim LastCell As Range
    Set LastCell = Cells(Rows.Count, 1).End(xlUp)
    LastCell.Offset(1, 0) = "竹達"
End Sub

Sub Macro6()
''トリム参照
    [TAKE(A:.A,-1)].Offset(1, 0) = "竹達"
End Sub

TAKE関数について詳しい解説は、下記のページをご覧ください。

TAKE関数/DROP関数の解説

先に、「Cells(Rows.Count, 1).End(xlUp).Row」部分について、"意味が分からない"という方が多いと書きましたが、ここも含まれます。上記Macro5では、End(xlUp)の後ろに「.Row」がありません。「.Rowが必要なときと、書いちゃダメなときの違いが分からない」と。私の「VBAベーシック」セミナーを受講された方でしたら、悩むことはないでしょう。ですが、そもそも「オブジェクト式とは」という基礎を学習していない方には意味不明だと思います。Endを使った書き方って、それくらい難しいんですよ。

解説は以上です。本稿のタイトル「もう最終行の取得にEndを使わない」ですが、本当は「もう最終行の取得にEndを使わない？」という気持ちです。サムネ詐欺ならぬタイトル詐欺みたいでした。すみません。もうEndを使うな、と言っているのではありません。今まではEndしか手がなかったけど、これからは別の選択肢も検討してください、ってのが本稿の主旨です。決めるのは、マクロ作成者です。正しく理解して、より適切なコードを書いてください。

もう最終行の取得にEndは使わない