初心者のためのExcel(エクセル)マクロVBA入門-行末の取得・終端セルを参照する（CountA関数、Endプロパティ）

Find使わないとどうなるか？

今回はいきなり回答を出します。これ。

' abcを検索して色を黄色に変えるマクロです。
Public Sub test1()
        
    ' 繰り返しで使うカウンタの変数を作成
    Dim i As Long
    
    '全部検索するまで繰り返します。
    For i = 1 To 500
    
        'セルの値でabcが含まれているかを調べる
        If InStr(1, Sheet1.Cells(i, 1).Value, "abc") > 0 Then
        
            ' セルの色を黄色にする
            Sheet1.Cells(i, 1).Interior.ColorIndex = 6
        
        End If
        
    Next
        
End Sub

前回よりも全然行数が少ないのでこっちの方が分かりやすいかもしれません。
For文で1行目から500行まで繰り返しています。InStrは文字列から特定の文字列を探し出して、その見つかった最初の位置（何文字目とか）を返す「関数」です。具体的にはヘルプを見ましょう。

「関数」については以前プログラミングの基礎でやりましたね。InStrは関数でVBAで用意されているものです。一番大事な条件式If文を見てみます。
InStr関数で各セルの値からabcを検索し、その関数の戻り値が0より大きければ、つまりabcが見つかった場合に、セルの色を黄色にしています。0またはそれ以下の場合は見つからなかった場合なので何もしません。簡単ですね。

500行以上あったらどうするの？

ここで疑問がわきます。さて、1000行だったらどうでしょうか？For文の500を1000に変えればいいだけです。
しかし、毎回たとえば1000行だったり、1235行だったり、はたまた少なくて100行だったりと毎回変わったらどうでしょうか？これを実行するたびに書き換えるのはちょっと面倒くさいです。そこで、常にデータの一番終端をとる方法があります。
それが、CountAまたはEndプロパティです。

ワークシート関数のCountAはデータの個数を取得する。

CountAはある範囲に入っているデータの個数を返すまたまた「関数」です。データの範囲という入力（y=f(x)のx）を与えればその範囲に入っているデータの個数（y=f(x)のy）を返してくれます。したがって、データが隙間なく入っている場合にはこの関数を使うとデータの終端が分かります。

    
    '全部検索するまで繰り返します。
    For i = 1 To WorksheetFunction.CountA(Sheet1.Columns(1))

こうなります。CountAのカッコの中にSheet1.Columns(1)はA列を指定します。Sheet1オブジェクトのColumnsプロパティで1列目を指定していると考えるといいですね。

Endプロパティは最終行から上に上がって最初の行を取得する方法

続いてEndプロパティですが、ちょっと文章ではわかりにくいかもしれないですね。。。。要はExcelの最終行（2002なら65536行、2007以降なら1048576行)から上にCtrl+↑キーを押したときと同じ動作をする。ということになります。
やってみましょう。

    
    '全部検索するまで繰り返します。
    For i = 1 To Sheet1.Range("A1048576").End(xlUp).Row

この式で、A1048576行目から、Ctrl+↑キーで移動した結果の行が入ります。これで、最後の行がいくつになっても自動的にすべて実行してくれるわけです。

どっちがいいの？

さて、前回書いたFind関数を使ったマクロと今回のマクロ。どっちが「優秀」でしょう？プログラム的には今回の方が「きれいに見える」と思いますが、残念ながらExcelに関して言えば前回の方が実は「優秀」です。
理由は「検索を実行する回数が少ないから」です。つまりパフォーマンスとしては前回の方がよいマクロということです。今回はプログラム自体は短いのですが、実行すると、結局「全部の行」を比較しています。500行なら500回、10000行なら10000回繰り返してabcを1つ1つ検索しているわけです。
しかし前回のであればFindでabcがあるだけしか検索しません。つまり比較する行数が多くなればなるほど、パフォーマンスに対する影響が大きくなるということです。

プログラミングはこのように実行される回数をより減らす努力をしないといけません。

ただし、500行や1000行程度ならほとんど変わらないので、今回の方がお手軽簡単ということで軍配は上がります。結局ケースバイケースなのですが、両方知らないと、たくさん処理する場合には大変ですよ。ということです。

次回は、ユーザービリティ！についていよいよマクロっぽくボタンとか設置してみますよー！

かしこ