考虑一下这个表单：

使用VBA，你可以像这样获取数据：

Sub GetValues()
    Dim arrValues()
    
    arrValues = Range("A2:A10")
    
    For Each c In arrValues
        Debug.Print c
    Next
End Sub

range可以直接分配给数组。数组项将保存调用数据。
输出：

value1
value2
value3
value4
value5
value6
value7
value8
value9

要有效地将范围或2D数组展平为1D数组（不包括标头），可以使用以下方法：

Function FlattenRange(rng As Range) As Variant
Dim dataRange As Range
Dim dataArray As Variant
Dim resultArray As Variant
Dim i As Long

' Exclude the header row
Set dataRange = rng.Offset(1).Resize(rng.Rows.Count - 1)

' Convert the range to a 2D array
dataArray = dataRange.Value

' Determine the size of the result array
ReDim resultArray(1 To UBound(dataArray, 1))

' Copy the values from the 2D array to the result array
For i = 1 To UBound(dataArray, 1)
    resultArray(i) = dataArray(i, 1)
Next i

FlattenRange = resultArray
End Function

要使用此函数，您可以使用范围作为参数调用它，并将返回的1D数组分配给变量。下面是一个例子：

Sub TestFlattenRange()
Dim rng As Range
Dim flattenedArray As Variant

' Define your range
Set rng = Range("A1:A10")

' Call the FlattenRange function
flattenedArray = FlattenRange(rng)

' Display the flattened array values
Dim i As Long
For i = LBound(flattenedArray) To UBound(flattenedArray)
    Debug.Print flattenedArray(i)
Next i
End Sub

此代码将排除标题行，并将范围转换为包含其余行中的值的1D数组（flattenedArray）。您可以修改它以满足您的特定需求。

函数返回一个一维数组，数组范围为rng，可以忽略第一个hdr_rows单元格数：

Function range_to_1d_array(rng As Range, Optional hdr_rows As Long = 0) As Variant
    Set BodyRange = rng.Offset(hdr_rows).Resize(rng.Rows.Count - hdr_rows)
    range_to_1d_array = Application.Transpose(Application.Index(BodyRange, 0, 1))
End Function

要使用此选项读取A1：A10（忽略第1行）：

myArr = range_to_1d_array(rng:=Range("A1:A10"), hdr_rows:=1)

要查看此操作：

Sub test()
    myArr = range_to_1d_array(rng:=Range("A1:A10"), hdr_rows:=1)
    For Each arrayvalue In myArr
        Debug.Print arrayvalue
    Next
End Sub

或者，您可以使用以下命令请求特定值：

myArr = range_to_1d_array(rng:=Range("A1:A10"), hdr_rows:=1)
    thirdvalue = myArr(3)

单列值转一维数组

Sub ColumnToArray()

    Const FIRST_CELL As String = "A1"
    Const HEADER_ROWS As Long = 1
    Const ARRAY_LOWER_LIMIT As Long = 0
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
     
    Dim rg As Range, rCount As Long
    With ws.Range(FIRST_CELL)
        rCount = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row - .Row
        If rCount < HEADER_ROWS Then Exit Sub ' nothing or just headers
        Set rg = .Offset(HEADER_ROWS).Resize(rCount)
    End With
    
    Dim Data()
        
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    Dim rDiff As Long: rDiff = 1 - ARRAY_LOWER_LIMIT
    Dim Arr(): ReDim Arr(ARRAY_LOWER_LIMIT To rCount - rDiff)
    
    Dim r As Long
    
    For r = 1 To rCount
        Arr(r - rDiff) = Data(r, 1)
    Next r
    
    Debug.Print Join(Arr, ", ") & vbLf _
        & "Array Limits [" & LBound(Arr) & "," & UBound(Arr) & "]"
    
End Sub

结果

1, 2, 5, 3, 7, 9, , , 10
Array Limits [0,8]