【VBA】ReDim Preserveで2次元配列の行が増やせない原因と対処

前回の記事では、配列が空（未割当）かどうかを判定する方法を紹介しました。

配列を安全に扱えるようになると、次は「配列にデータを足していく」処理を書きたくなります。

1次元配列なら、ReDim Preserve で要素を増やせます。ところが、2次元配列で行を増やそうとすると、実行時エラー9で止まります。

この記事では、その原因と、私が実務で使っている回避パターンを紹介します。

困った場面：ReDim Preserveで2次元配列の行を増やすとエラーになる

処理結果を2次元配列にためていく場面を考えます。行数は、処理してみないと分かりません。

そこで、必要になったら1行増やすつもりで、次のように書きました。

Sub AddRowNG()
    Dim list() As Variant

    ReDim list(1 To 3, 1 To 2)      ' 3行×2列
    list(1, 1) = "A001"
    list(1, 2) = "東京"

    ' 1行増やして4行にしたい
    ReDim Preserve list(1 To 4, 1 To 2)
End Sub

実行すると、ReDim Preserve の行で次のエラーが出ます。

実行時エラー ‘9’:

インデックスが有効範囲にありません。

Preserve を外せばエラーは出ません。ただし、配列の中身はすべて消えます。

原因：変えられるのは「最後の次元の上限」だけ

ReDim Preserve には制約があります。サイズを変えられるのは、最後の次元の上限だけです。

list(行, 列) の形なら、最後の次元は「列」です。つまり、列は増やせますが、行は増やせません。

やりたいこと	書き方の例	結果
列（最後の次元）を増やす	ReDim Preserve list(1 To 3, 1 To 3)	成功。中身も残る
行（最初の次元）を増やす	ReDim Preserve list(1 To 4, 1 To 2)	実行時エラー9
次元の数を変える	ReDim Preserve list(1 To 3, 1 To 2, 1 To 2)	実行時エラー9
下限を変える	ReDim Preserve list(1 To 3, 0 To 2)	実行時エラー9
Preserve なしでサイズを変える	ReDim list(1 To 4, 1 To 2)	成功。ただし中身は消える

セル範囲を読み込んだ Variant の配列でも、結果は同じです。

この仕様は、Microsoft の公式リファレンスにも書かれています。

• ReDim ステートメント (VBA) | Microsoft Learn

実務で足したいのは、ほとんどの場合「行」です。そのため、2次元配列では ReDim Preserve がほぼ使えません。

解決コード：最大サイズで確保して、最後に切り出す

回避の手順は3つです。

1. 想定される最大の行数で、先に配列を確保する
2. 件数を数えながら、データを詰めていく
3. 件数が確定したら、正しいサイズの配列に切り出す

「データ」シートから、3列目が「未処理」の行だけを抜き出す例です。

Sub ExtractRows()
    Dim src As Variant        ' 元データ（1行目はヘッダー。データは2行以上ある前提）
    Dim buf() As Variant      ' 最大サイズで確保する作業用の配列
    Dim result() As Variant   ' 正しいサイズに切り出した結果
    Dim cnt As Long
    Dim i As Long, j As Long

    src = Worksheets("データ").Range("A1").CurrentRegion.Value

    ' 1. 最大サイズで確保する（抽出結果は元データの行数を超えない）
    ReDim buf(1 To UBound(src, 1), 1 To UBound(src, 2))

    ' 2. 条件に合う行だけ詰めていく（ヘッダー行は除く）
    For i = LBound(src, 1) + 1 To UBound(src, 1)
        If src(i, 3) = "未処理" Then
            cnt = cnt + 1
            For j = 1 To UBound(src, 2)
                buf(cnt, j) = src(i, j)
            Next j
        End If
    Next i

    ' 0件なら切り出さずに終了する
    If cnt = 0 Then
        MsgBox "該当するデータはありません。"
        Exit Sub
    End If

    ' 3. 正しいサイズに切り出す
    result = SliceRows(buf, cnt)

    ' ここから先は、result を後続の処理に渡す
    MsgBox UBound(result, 1) & "件を抽出しました。"
End Sub

' 2次元配列の先頭から cnt 行だけを取り出して返す
Function SliceRows(ByRef buf() As Variant, ByVal cnt As Long) As Variant()
    Dim result() As Variant
    Dim i As Long, j As Long

    ReDim result(1 To cnt, LBound(buf, 2) To UBound(buf, 2))
    For i = 1 To cnt
        For j = LBound(buf, 2) To UBound(buf, 2)
            result(i, j) = buf(i, j)
        Next j
    Next i

    SliceRows = result
End Function

解説

最大サイズの決め方

抽出や絞り込みの処理なら、結果が元データの行数を超えることはありません。元データの行数を、そのまま最大サイズにできます。

切り出しておくと、UBoundがそのまま件数になる

最大サイズのままの配列を後続の処理に渡すと、UBound が実際の件数と一致しません。ループが空の行まで回ってしまいます。

切り出しておけば、UBound(result, 1) をそのまま件数として使えます。

なお、シートに書き出すだけなら、切り出しは不要です。Resize で件数分の範囲を指定すれば、先頭の行だけが書き込まれます。

Worksheets("結果").Range("A1").Resize(cnt, UBound(buf, 2)).Value = buf

0件のときは切り出さない

件数が0のまま SliceRows を呼ぶと、ReDim result(1 To 0, …) となり、ここでも実行時エラー9が出ます。切り出す前に、必ず件数を確認してください。

0件のとき、result は未割当のままです。後続の処理で判定が必要なら、前回の記事の方法が使えます。

上限が読めないときは

外部から届くデータを順にためる場合など、最大サイズが読めないこともあります。そのときは、別の方法を使います。

• 新しい配列にコピーしながら、1行ずつ追記する
• 先に件数を数えてから、ぴったりのサイズで確保する
• ユーザー定義型（Type）の1次元配列を使う

先に件数を数えてから確保する方法は2次元配列の行数が決まらないときの対処法まとめで、ユーザー定義型（Type）の1次元配列を使う方法はType配列で件数不明のデータをためる方法で、それぞれ紹介しています。新しい配列にコピーしながら1行ずつ追記する方法も、比較まとめ記事の中で触れています。

まとめ

• ReDim Preserve で変えられるのは、最後の次元の上限だけ
• 2次元配列の行を増やそうとすると、実行時エラー9になる
• 最大サイズで確保し、件数が確定してから切り出す
• 0件のときは切り出さない