Excel VBA无法将超过65535行的Excel数据复制到数组问题求助

问题根源 #

你遇到的65535行限制是因为Application.Transpose函数的兼容性限制——这个函数是为旧版Excel（97-2003，最大行65536）设计的，当处理超过65536行的数据时，它会自动截断到65535行，这就是为什么你无法复制到1048576行的原因。

另外还有个小细节需要注意：你的变量firstRowData和firstColumnData用了Integer类型，而Excel的行号最大是1048576，远超Integer的最大值（32767），容易引发溢出错误，建议改成Long类型更安全。

解决方案 #

根据你的需求，分两种情况处理：

1. 不需要转置数组（直接存储原始行列结构）

如果你的数组不需要转置行和列，直接把Range的值赋值给数组即可，这是最直接且无行数限制的方式：

Dim firstRowData As Long: firstRowData = 2
Dim firstColumnData As Long: firstColumnData = 1
Dim LastUsedRowData As Long: LastUsedRowData = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
Dim lastcolumnData As Long: lastcolumnData = sht.Cells(firstRowData, sht.Columns.Count).End(xlToLeft).Column
Dim rangeSelection As Range, arraystoreMasterData() As Variant

' 注意要通过sht引用Cells，避免ActiveSheet的潜在问题
Set rangeSelection = sht.Range(sht.Cells(firstRowData, firstColumnData), sht.Cells(LastUsedRowData, lastcolumnData))

' 直接赋值，得到二维数组：arraystoreMasterData(行号, 列号)
arraystoreMasterData = rangeSelection.Value

2. 需要转置数组（行转列/列转行）

如果你确实需要转置数组结构，不要使用Application.Transpose，而是自己实现一个支持大数组的转置函数：

' 自定义支持大数组的转置函数
Function TransposeLargeArray(inputArr As Variant) As Variant
    Dim i As Long, j As Long
    Dim rowCount As Long, colCount As Long
    
    ' 获取输入数组的行列数量
    rowCount = UBound(inputArr, 1) - LBound(inputArr, 1) + 1
    colCount = UBound(inputArr, 2) - LBound(inputArr, 2) + 1
    
    ' 初始化转置后的数组
    ReDim outputArr(1 To colCount, 1 To rowCount)
    
    ' 逐元素完成转置
    For i = LBound(inputArr, 1) To UBound(inputArr, 1)
        For j = LBound(inputArr, 2) To UBound(inputArr, 2)
            outputArr(j, i) = inputArr(i, j)
        Next j
    Next i
    
    TransposeLargeArray = outputArr
End Function

然后在主代码中调用这个函数：

' 先获取原始数组（无行数限制）
arraystoreMasterData = rangeSelection.Value
' 调用自定义转置函数处理大数组
arraystoreMasterData = TransposeLargeArray(arraystoreMasterData)

额外注意事项 #

• 始终通过工作表对象（比如sht）引用Range和Cells，避免依赖ActiveSheet，防止切换工作表时出现错误。
• 所有涉及行号、列号的变量都用Long类型，避免因数值超出Integer范围导致的溢出错误。

内容的提问来源于stack exchange，提问作者Procoder