Excel/VBA: How do I convert a single row into an array of the cell values-CodePudding

I thought this would be simple, but apparently the geniuses at Microsoft think differently.

I am writing Excel VBA to loop through the rows in an Excel worksheet and for each row (a Range object), I want to extract the cell values as an array. I've done some searching and found some supposed solutions that are not at all intuitive, nor do they work.

Does anyone have suggestions on how to do this?

Private Sub Separate_By_DC()
    Dim row_ As Range
    Dim row_str As String
    
    For Each row_ In ActiveSheet.UsedRange.Rows
        Dim arr As Variant
        arr = Row_To_Array(row_)
        Debug.Print UBound(arr) - LBound(arr)   1
        
        ' row_str = Concat_Row(row_)
        ' Debug.Print row_str
    Next row_
End Sub

Private Function Row_To_Array(row_ As Range) As Variant
    Row_To_Array = row_.Value
End Function

CodePudding user response：

Think the most easiest way would be:

Sub test()

Dim mArr As Variant
Dim i As Long

mArr = Application.Transpose(Range("A1:A10")) 'Transpoose to make the array one dimensional

'loop through the array
For i = LBound(mArr, 1) To UBound(mArr, 1) 'For one dimensional you can remove ",1"
    'do something with mArr value
    Cells(i, "C").Value = mArr(i) 'Print the array value
Next i

End Sub

For 2 dimensional array you can loop through it by state the dimensional index:

Dim mArr As Variant
Dim i As Long

mArr = Range("A1:A10") 'Use 2 dimension

'loop through the array
For i = LBound(mArr, 1) To UBound(mArr, 1)
    'do something with mArr value
    Cells(i, "C").Value = mArr(i, 1)
Next i