Row values copied into Variant as String, when using For Each on Rows collection

user_111794 0
Viewed 51

I know that it is possible to get all the values from an Excel Range into an array using the Value property:

Dim arr As Variant
arr = ActiveSheet.UsedRange.Value
Debug.Print TypeName(arr)
' prints Variant()

However, when I try to iterate over the rows in the range (via the Rows property), and similarly assign the Value to a Variant, I get a text-delimited string:

Dim row As Range
For Each row In ActiveSheet.UsedRange
    arr = row.Value
    Debug.Print TypeName(arr)
    Exit Sub
Next
' prints String

Not sure if this is related, but the VBA IDE reports the type of row as Range/Range, and the type of ActiveSheet.UsedRange as Variant/Object/Range. TypeName in both cases returns Range.

Why is this different, and how can I get the first behavior?

(I am aware that I could probably Split the string into an array, but I don't want to do this.)

vba excel
2 Answers

You aren't looping through rows, you are looping through cells.

So, TypeName will return the data type of the value in the first cell.

If you change the code to this then arr will be of type Variant().

Dim row As Range
For Each row In ActiveSheet.UsedRange.Rows
    arr = row.Value
    Debug.Print TypeName(arr)
    Exit Sub
Next
user_28500260

This:

Dim row As Range
For Each row In ActiveSheet.UsedRange

is the same as:

Dim row As Range
For Each row In ActiveSheet.UsedRange.Cells

when maybe you expected this:

Dim row As Range
For Each row In ActiveSheet.UsedRange.Rows 'need to be explicit about this....

The .Value of a single-cell range isn't returned as an array

user_4788840
Related