2次元配列の1次元目の要素数の設定

Question

●やりたいこと（一応、できている）
A列１行目から最終行（1045876）まですべてのセルに１～１００までの数字がランダムで入ってます。（header なし）
B列１行目から最終行（1045876）まですべてのセルに　boy or girl のどちらかがランダムで入ってます。（header なし）
この中から、条件（A列50以上B列boy)に合致する行のみをD列とE列に転記したい。

●質問
条件に合致した行だけ、配列の要素数が必要になるが、その要素数がいくつになるかわからない。
その場合、配列の要素数の初期値は実際に必要となる要素数よりも少ないとエラーが出るが要素数が多い分には、問題はないのか？

下記コードのように、配列の1次元の要素数を最終行（1 to rows.count）としました。
つまり、すべての行が条件に合致した場合にも、要素数が足りるようにしています。
実際には、もっと少ない行数が条件に合致するため、この1,045,876も必要ありません。
しかし、これはこれで、間違ってはいないやり方なのでしょうか？

最初に自分で考えたやり方は、初期値を(1 to1 ,1 to2)にしておき、
条件に合致した行のみ、配列の一番上の要素数に入れる、そして、redim preserveで1次元目の要素数を１つ足すというやり方をやろうとしました。しかし、調べたら、皆さんご指摘の通り、1次元目の要素数は、変更できないということがわかり、上記のようなやり方をとりました。

もっとスマートなやり方は他にありますでしょうか？
ちなみに、転記件数は、全体の半分のおよそ52万件で30秒くらいかかりました。
十分早いのでしょうか？

Sub hairetsu()

Dim i, v, j, z
Dim ary()
j = 1
ReDim ary(1 To Rows.Count, 1 To 2)
For i = 1 To Rows.Count
    If Cells(i, 1) >= 50 And Cells(i, 2) = "boy" Then
    For v = 1 To UBound(ary, 2)
        ary(j, v) = Cells(i, v)
    Next v
    j = j + 1
    End If
    
Next i
Cells(1, 4).Resize(UBound(ary, 1), UBound(ary, 2)) = ary
End Sub

Answer 1

速いかどうかはわかりませんが。

ReDim ary(1 To WorksheetFunction.CountIfs(Columns(1), ">=50", Columns(2), "boy"), 1 To 2)

Comments

[robben]

jinoji様
ありがとうございます。
countifsの返り値が必要な1次元の要素数ですね。これの方がよさそうですね。

ちなみに、私の質問の中にある、初期の要素数を（必要ないけども）行数に設定するというのも決して間違いではないですか？

[jinoji]

間違いではないと思います。
ReDim Preserveで逐一増やす方法より速いこともあるかもしれないです。

[robben]

要するに、格納する配列の要素数とセルの数は（多い分には）必ずしも一致しなくてもいいという理解であってますか？

[jinoji]

そうですね。
小物１点でも大きな段ボールに入れて発送してくるAmazonみたいなものです。
消費者目線ではちゃんと届くから問題ないし、送料無料だから損しているわけでもないが、無駄で邪魔に思える。一方でAmazon側から見れば、倉庫での取り回しの面などからそれが合理的だったりするかもしれない。

要するに、格納する配列の要素数とセルの数は（多い分には）必ずしも一致しなくてもいいということだと思います。

[robben]

ありがとうございました！すっきりしました。

Answer 2

速度を改善したいのなら、セルに一つずつアクセスするのは重くなるので、
データセル範囲を配列に格納して、配列で処理すると高速になります。

Public Sub ArrayTest()

    Dim ary
    ary = Cells(1, 1).Resize(Rows.Count, 2).Value 'セル範囲を配列に格納

    Dim i As Long, j As Long, v As Long
    For i = 1 To Rows.Count
        If ary(i, 1) >= 50 And ary(i, 2) = "boy" Then
            '一致したデータを前に詰める
            j = j + 1
            For v = 1 To UBound(ary, 2)
                ary(j, v) = ary(i, v)
            Next v
        End If
    Next

    '配列のサイズより小さいセル範囲に代入するとはみ出た分は切り捨てられる
    Cells(1, 4).Resize(j, 2).Value = ary

End Sub

当方でサンプル作成してテストしてみたら、1/5ぐらいの時間で完了しました。

Answer 3

for文とか使うよりエクセルのシートに計算式入れて列ごとコピーをマクロ記録した方がいいと思う。