将数据集直接加载到数组Excel VBA中

Sub LoadDataToArray()
    Dim ws As Worksheet
    Dim data() As Variant
    Dim lastRow As Long, lastCol As Long
    
    ' 设置工作表对象
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 获取数据的最后一行和最后一列
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 将数据加载到数组中
    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value
    
    ' 输出数组中的数据
    Dim i As Long, j As Long
    For i = 1 To UBound(data, 1)
        For j = 1 To UBound(data, 2)
            Debug.Print data(i, j)
        Next j
    Next i
End Sub

ReDim Preserve data(1 To lastRow, 1 To lastCol)

data = Application.WorksheetFunction.Transpose(ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value)

' 使用Excel的内置函数进行数据处理
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Sort Key1:=ws.Range("A:A"), Order1:=xlAscending, Header:=xlYes