Transeposeの配列数が多い場合の#N/A挙動について

Question

発生している問題

wsKey1Colはキーとなる項目が格納されている列数のカラム番号です。
この列に格納されているデータの総数が115693件以下の場合は後述のプログラムでうまくいきます。

130460件の場合はTransposeの貼り付け先に#N/Aが出力されて貼り付け自体中途半端な行で終わっている状況になります。

貼り付け先のシートに#N/Aが存在する。(8044行目以降すべてのセルにN/A)

調べたところ
Transposeの元データと貼り付け先の範囲が異なるとエラーとなるとありますが、
ディクショナリのデータ格納数でResizeしているので、その線は薄いです。

https://answers.microsoft.com/ja-jp/msoffice/forum/all/transpose%e9%96%a2%e6%95%b0%e3%82%92%e7%94%a8/5d7db3f0-9165-4e93-9a67-7044f5722820

別のサイトでは、配列数が多い場合にTransposeはうまくいかないといった記述を見つけました。

https://excel-ubara.com/excelvba4/EXCEL268.html

処理速度を変えることなく
Transposeの代用として使用できる関数はありますでしょうか。

該当のソースコード

Dim rngA As Range
Set rngA = Worksheets("作業シート合算").Cells(1).CurrentRegion.Offset(1)
Set rngA = rngA.Resize(rngA.Rows.Count - wsGassanHeadder)
       
Dim D1 As Object
Set D1 = CreateObject("Scripting.Dictionary")
    
Dim r As Range
For Each r In rngA.Rows
　  D1(r.Cells(1, wsKey1Col).Text) = D1(r.Cells(1, wsKey1Col).Text) + r.Cells(1, wsFileSumCol)
Next

With Worksheets("作業シート合算")
    'Dictionaryのキー配列を作業シート合算に出力
    .Range("K2").Resize(D1.Count).Value = WorksheetFunction.Transpose(D1.Keys)
    'Dictionaryのアイテム配列(集計値)を作業シート合算に出力
    .Range("M2").Resize(D1.Count).Value = WorksheetFunction.Transpose(D1.items)

試したこと

件数が少ない場合は上記コードで意図した動きができることの確認

件数が多い場合は上記コードでTransposeの貼り付け先に#N/Aが出現することの確認

Answer 1

処理速度は変わりますが、普通に配列に入れてセットするので問題ないと思います。

簡単ですが、一応コードを書きますと

Dim v1 As Variant
Dim v2 As Variant
Dim tmpKeys As Variant
Dim tmpItems As Variant
ReDim v1(D1.Count - 1, 0)
ReDim v2(D1.Count - 1, 0)
tmpKeys = D1.Keys
tmpItems = D1.Items

Dim i As Long
For i = 0 To D1.Count - 1
    v1(i, 0) = tmpKeys(i)
    v2(i, 0) = tmpItems(i)
Next
    
With Worksheets("作業シート合算")
    .Range("K2").Resize(D1.Count).Value = v1
    .Range("M2").Resize(D1.Count).Value = v2
End With

あと、質問とは関係のない所ですが
この処理速度の変化よりも、Dictionaryに値をセットする所を修正することによる速度改善の方が大きいと思います。

Dim r As Range
Dim tmpKey As String
For Each r In rngA.Rows
    tmpKey = r.Cells(1, wsKey1Col).Text
    D1(tmpKey) = D1(tmpKey) + r.Cells(1, wsFileSumCol).Value
Next

劇的に早くなるわけではありませんが、テストした所
元のコードでは１分３０秒ちょっとかかったのが１分１０秒で処理が終わりました。