高速！ワークシートに出力するにはデータを配列に入れて一気に出力がおススメ！

ワークシートに大量の書き込みをするときに、シートに1セル1セルに出力すると、ものすごく時間がかかるので、配列にデータを入れ込んでおいて一気にワークシートにがばっと入れる、というのをよくやります。早いです。

１．基本のやり方、注意点
２．セル範囲を配列に入れた場合
- （１）2次元配列の場合
- （２）データが1列の場合
３．セル範囲を一気に入れる方法ではない場合
- （１）二次元配列の場合
- （２）1次元配列の場合
４．ReDimして配列の要素数を増やしながら作成した配列の場合

１．基本のやり方、注意点

データを配列に入れて、シートに一気に出力する方法。

気を付けるポイント

出力するセル範囲は、配列の大きさと同じにすること
配列内の並びに気を付ける（出力する方向、向き）

例えば、2列5行のデータを別のシートのセルA1に出力したい場合。

①配列に入れる
arr = Range(“A1:B5”).Value
②シート2のセルA1から出力
With WorkSheets(2)
.Range(“A1:B5”).Value = arr
ENd With
と、します。コピーして貼り付けするときのように、貼り付け起点の一番左上のセルだけを指定すればよい、というわけにはいかず、ちゃんとセル範囲を指定します。

配列の向きにも注意が必要です。
2次元配列の出力イメージはこんな感じです()内は要素の番号を示しています。
（わかりにくいですかね？）

一次元配列はこうです。

結構、縦に出てほしい・・・と思うことが多いので、縦横を変換したいときはワークシート関数のTransposeを使用します。
WorkSheetFunction.Transpose(配列)
と書きます。

Transposeを使用したときのイメージは下図の感じです。

Transposeは便利です。ですが、データが65,536を超える場合は、おかしくなりますので6万を超えそうなときは、Transposeは使用せず、代替手段を使用することをお勧めします。
【Excel/VBA】配列をTransposeしたらちょん切れたので検証と回避方法を模索

次項から実際の配列への代入、ワークシートへの出力方法をご紹介します。

２．セル範囲を配列に入れた場合

（１）2次元配列の場合

例えば、下図のSheet1の20行2列のデータをSheet2に転記するのに、配列に入れて、その配列をがばっと入れるコード。（全然大量じゃないけど）

Sub test()
    Dim arr As Variant
    'シート1のデータを配列arrに代入
    With Worksheets(1)
        arr = .Range("A1:B20").Value
    End With
    '配列arrをシート2に出力
    With Worksheets(2)
        .Range("A1:B20").Value = arr
    End With
End Sub

上記を途中で止めて配列の中身をローカルウインドウで見るとこのようになっています。

コードを上から解説します。

Dim arr As Variant

配列arrを宣言します。セル範囲を一気に入れるときはVariant型にします。
Variant型にしないと配列代入時にエラーになります。
arrの後の()も不要です。（つけても怒られはしませんでした）

'シート1のデータを配列arrに代入
With Worksheets(1)
    arr = .Range("A1:B20").Value
End With

なお、上記のように配列に一気にセル範囲の値を入れた場合、配列は1始まりになります。

配列arrに指定したセル範囲の値を代入します。
セル範囲.Valueとすることで値だけが入ります。「セル範囲.Value」とすることで余計な情報が入らないような気がしてそのようにしていますが、.Valueはなくてもよいのかもしれません。

'配列arrをシート2に出力
With Worksheets(2)
    .Range("A1:B20").Value = arr
End With

配列arrをシート２に出力します。
前項でも述べましたが、配列と同じ大きさのセル範囲を指定します。
配列の大きさを変数で受け取ってセル範囲を指定する場合の例は以下のコードになります。

Dim rowEnd As Long, colEnd As Long
rowEnd = UBound(arr, 1)     '配列縦方向の大きさ
colEnd = UBound(arr, 2)     '配列横方向の大きさ
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(rowEnd, colEnd)).Value = arr
End With

UBound(arr, 1) 　で配列の1要素目の最大インデックス番号を取り出します。1は省略可能です。
UBound(arr, 2) 　で配列の2要素目の最大インデックス番号を取り出します。
セル範囲を配列に入れた場合、配列が1始まりになります。行列も1始まりなので、配列の最終要素数と最終行列の番号は一致します。（0始まりの配列の場合は1をプラスしないと帳尻が合いません）

なお、出力起点セルとResizeを使用して、以下のように書くこともできます。この方がシンプルですね。

With Worksheets(2)
    .Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With

（２）データが1列の場合

１列の場合でもやってみます。セル範囲を配列に入れた場合、1列のデータであっても１次元配列にはならず、2次元配列になります。

Sub test1()
    'シート1のデータを配列arrに代入
    Dim arr As Variant
    With Worksheets(1)
        arr = .Range("A1:A20").Value
    End With
        
    '配列arrをシート2に出力
    With Worksheets(2)
        .Range("A1:A20").value = arr
    End With
End Sub

３．セル範囲を一気に入れる方法ではない場合

セル範囲を一気に配列に入れるのが最も簡単ですが、シートにデータがあるわけではない等何らかの事情で配列に一つ一つデータを代入していった場合の手順です。

（１）二次元配列の場合

Sub test2()
    '配列を宣言
    Dim arr(3, 1) As Variant
    'データを配列arrに代入
    arr(0, 0) = 1
    arr(0, 1) = "a"
    arr(1, 0) = 2
    arr(1, 1) = "b"
    arr(2, 0) = 3
    arr(2, 1) = "c"
    arr(3, 0) = 4
    arr(3, 1) = "b"
    
    '配列arrをシート2に出力
    Dim rowEnd As Long, colEnd As Long
    rowEnd = UBound(arr) + 1        '最大要素数+1
    colEnd = UBound(arr, 2) + 1     '最大要素数+1
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(rowEnd, colEnd)).Value = arr
    End With

End Sub

出力結果はこのようになります。

上からコードを説明します。

'配列を宣言
Dim arr(3, 1) As Variant

配列を宣言します。縦に4つ、横に2つデータを格納する配列です。0始まりにするため、要素数は-1した数で宣言します。出力する方向に関係するので縦横には注意します。

'データを配列arrに代入
    arr(0, 0) = 1
    arr(0, 1) = "a"
    arr(1, 0) = 2
    arr(1, 1) = "b"
    arr(2, 0) = 3
    arr(2, 1) = "c"
    arr(3, 0) = 4
    arr(3, 1) = "b"

配列arrにデータを入れていきます。

'配列arrをシート2に出力
Dim rowEnd As Long, colEnd As Long
rowEnd = UBound(arr) + 1        '最大要素数+1
colEnd = UBound(arr, 2) + 1     '最大要素数+1
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(rowEnd, colEnd)).Value = arr
End With

セル位置の指定はわかりきっているのですが、あえて変数を使っています。
配列が0始まりなので、最大要素数を取得し1をプラスします。

なお、出力するコードは、出力起点セルとResizeを使用して、以下のようにもかけます。

'配列がゼロ始まりの場合
With Worksheets(2)
    .Range("A1").Resize(UBound(arr, 1) + 1, UBound(arr, 2) + 1).Value = arr
End With

（２）1次元配列の場合

１次元配列の場合、ちょっとややこしくなります。配列が横方向に伸びていくためです。

最初に間違った例を書きます。

Sub test2_2()
    '配列を宣言
    Dim arr(4) As Variant
    'データを配列arrに代入
    arr(0) = "a"
    arr(1) = "b"
    arr(2) = "c"
    arr(3) = "d"
　　arr(4) = "e"
    
    '出力
    With Worksheets(2)
        Range("A1:A5").Value = arr
    End With
End Sub

この場合結果は下図のようになります。

横方向に出力セル範囲を指定すると正しく出ます。

'出力
    With Worksheets(2)
        Range("A1:E1").Value = arr
    End With

いや、縦に出したいんだ、という場合はTransposeを使用します。

'出力
With Worksheets(2)
    Range("A1:A5").Value = WorksheetFunction.Transpose(arr)
End With

一応、最後の縦横変換のコード全文は以下になります。

Sub test2_2()
    '配列を宣言
    Dim arr(4) As Variant
    'データを配列arrに代入
    arr(0) = "a"
    arr(1) = "b"
    arr(2) = "c"
    arr(3) = "d"
    arr(4) = "e"
    
    '出力
    With Worksheets(2)
        Range("A1:A5").Value = WorksheetFunction.Transpose(arr)
    End With

End Sub

４．ReDimして配列の要素数を増やしながら作成した配列の場合

何らかの条件がある場合など、配列の要素数が分からないので、ReDimして配列の要素数を増やしながら、データを配列に蓄積していく。のですが、いつくか注意点があります。

vbaの配列は最終次元しか変更できない。つまり2次元配列の場合は2次元目しか増やせない
横方向にデータを追加してくため、横に長い配列になる。たいていの場合縦横変換が必要。

1次元目の要素数をReDimで変更しようとすると怒られます。
※1回目のReDimでは1次元目も変更できるが、2回目以降は×。

なので、2番目の要素を増やすしかない。何らかの条件があることにして、A列の数値が2で割れる場合、配列に追加して、2シート目に出力することにする。

Sub test()
    Dim arr() As Variant
    Dim i As Long
    Dim idx  As Long: idx = 0
    '1シート目のA列の数値が2で割り切れる数値の場合、配列arr代入
    With Worksheets(1)
        For i = 1 To 20
            If .Cells(i, 1).Value Mod 2 = 0 Then
                '2番目の要素を増やす
                ReDim Preserve arr(1, idx)
                arr(0, idx) = .Cells(i, 1).Value
                arr(1, idx) = .Cells(i, 2).Value
                idx = idx + 1
            End If
        Next i
    End With
    
    Stop
    
    '2シート目に配列arrの内容を出力
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(.Cells(UBound(arr, 2) + 1, 2)).Value = WorkSheetFUnction.Transpose(arr)
    End With
    
End Sub

途中（Stopのところ）で止めて、配列arrの中身を見てみる。
エディターの「表示」→「ローカルウインドウ」で中身を見てみた。ふむふむ。

こんなイメージ

Dim arr() As Variant

まず、配列を要素数を決めずに宣言します。

Dim i As Long
Dim idx  As Long: idx = 0
'1シート目のA列の数値が2で割り切れる数値の場合、配列arr代入
With Worksheets(1)
    For i = 1 To 20
        If .Cells(i, 1).Value Mod 2 = 0 Then
            '2番目の要素を増やす
            ReDim Preserve arr(1, idx)
            arr(0, idx) = .Cells(i, 1).Value
            arr(1, idx) = .Cells(i, 2).Value
            idx = idx + 1
        End If
    Next i
End With

必要な変数を宣言します。iは行カウンタ、idxは配列のインデックス。最初に0を代入しておきます。
セルの値 Mod 2 = 0 つまり、セルの値が2で割り切れたら、配列に代入する処理に入ります。
ReDim Preserve arr(i,idx) で配列を再宣言します。idxは一ずつ増えるので、配列の2次元目が1ずつ増えていきます。Preserveも忘れずに。これを入れないとこれまで入れたデータが消えてしまいます。

シート2にセルA1から配列の2番目の要素の最大値（Ubound(arr,2)で2番目の要素の最大値を取り出します））までを出力します。
ちなみにUbound(arr）だと1番目の要素数の最大を出してしまうので「2」となってしまうので、Ubound(arr,2)と「2番目の要素ですよ」とするのを忘れずに。
出力結果は下図の通り。。。あれっ？おかしい。
そうです。配列は横方向に伸びているのです。

なので、2シート目に出力する部分を横方向に長くすると、出ます。

'2シート目に配列arrの内容を出力
With Worksheets(2)
    .Range(.Cells(1, 1), .Cells(.Cells(UBound(arr, 2) + 1, 2)).Value = WorksheetFunction.Transpose(arr)
End With

シート2にセルA1から配列の内容を出力します。
Ubound(arr,2)で2番目の要素の最大値を取り出します。配列は0始まりなので、1始まりの行数に合わせるため＋１します。
で、ここが肝心。配列をTransposeして縦横変換して出力します。