空白以外のセルを一瞬でカウントするには？（Tips-5）

VBAの勉強を始めて、これまでに学んだことや、思いついたアレやコレをメモっておきます。

今回は、VBAで、セルにデータが有るか無いか（または特定のデータが有るか無いか、でもいい）を調べて、そのデータ数をカウントする方法についてです。

まず、下のように、For Each ～Next を使って A1:Z1048576 の範囲にデータがいくつ存在しているかをカウントしてみようと思います。
セルが空白ではない場合に、cnt　を +1 します。

Sub test1()

    For Each myRange In Range("A1:Z1048576")
        If myRange.Value <> "" Then
            cnt = cnt + 1
        End If
    Next myRange
    
End Sub

おっと、その前に、処理にどのくらい時間がかかったのかを調べるために、時間計測できる仕掛けを組み込んでおきましょう。

Sub test1()

    StartTime = Time

    For Each myRange In Range("A1:Z1048576")
        If myRange.Value <> "" Then
            cnt = cnt + 1
        End If
    Next myRange
    
    StopTime = Time
    MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
    & "分" & Second(StopTime - StartTime) & "秒"
    
End Sub

さあ、これでかかった時間は･･････、

1分58秒

･･････結構、かかったね(＠_＠;)

次は、A1:Z1048576 の範囲を配列に代入してから、配列をループして調べる方法を試してみます。配列の中身が空白ではない場合に、cnt　を +1 します。

Sub test2()

    StartTime = Time

    Tdim = Range("A1:Z1048576")
    
    For i = 1 To 1048576
        For j = 1 To 26
            If Tdim(i, j) <> "" Then
                cnt = cnt + 1
            End If
        Next j
    Next i
    
    StopTime = Time
    MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
    & "分" & Second(StopTime - StartTime) & "秒"
    
End Sub

かかった時間は･･････、

5秒

圧倒的に速くなりましたよ！

この結果から、配列へのアクセスが速いこと（言い換えればループの都度、セルへアクセスすることが時間のかかること）が分かります。
ループというのは掛け算であり、ループの中に入っている処理が少し重いだけで、ループ回数によってはとんでもない時間を要してしまうんですよね･･････。

でも、セルにデータが有るか無いかを調べるのであれば、もっと速い方法がありますよね？

それは、ワークシート関数を利用することです。test1、test2と同じ処理をワークシート関数を使って、書き直してみましょう。
CountIfで、空白以外のセル数をカウントさせます。

Sub test3()

    StartTime = Time

    cnt = WorksheetFunction.CountIf(Range("A1:Z1048576"), "<>")
    
    StopTime = Time
    MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
    & "分" & Second(StopTime - StartTime) & "秒"
    
End Sub

かかった時間は･･････、

0秒

速い！しかも、コードがすっきりするというオマケつき。

test1～3について、それぞれにかかった時間をまとめると下のようになります。 

方法	時間
For Each ～Next で1セルずつ調べる	1分58秒
配列に格納してから調べる	5秒
ワークシート関数で調べる	0秒

こうやって見ると、ワークシート関数が圧倒的に速いんですよね。

いや、けして、ワークシート関数利用推進派の回し者ではありませんよ。

おまけ：
今回のコードでは、A1:Z1048576 のセル範囲を試験的に調べましたが、じゃあ、A1:XFD1048576 の範囲（要するにシート内のすべてのセル）だったらどのくらい時間がかかるの？ということで、補足しておきます。
私のPCスペックでは、test1のケースで、単純計算で20時間かかり、test2のケースでは、メモリ不足のためテストすることすらできません。
しかし、test3のワークシート関数であれば、なんと！それでも 0秒 で処理が完了してしまいます。普段、ワークシート関数を使う分には何の疑問も抱きませんが、こうやって他の方法と比較してみると、「どうやって処理してるの？」と不思議に思ったりもします。

次回は、ワークシート関数以外で、ループ処理をなるべく使わずに済む方法を模索したり、ループ処理をどうしても使わなければならない場面で、ループ回数の掛け算の被乗数側となる処理をできる限り軽くする方法などについて考えてみたいと思います(＠_＠;)