1d Variant arrays?

[Guest]

Can this be done?
....Create a 1d variant array from a range using an assignment directly to
the array like the 2d assignment statement?

'***test code***
Option Explicit
Option Base 1
Sub Array_Play()
Dim i As Integer, j As Integer, k As Integer
Dim vaData As Variant

'ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$B$28" 'IS a 2d range
ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$A$28" 'IS a 1d range

i = Range("rngItemNo").Rows.Count
j = Range("rngItemNo").Columns.Count

If j > 1 Then
'ReDim vaData(i, j) '<- handled automatically by the next stmt
vaData = Range("rngItemNo").Value
GoTo ArrayDone
End If

' still makes a 2d array... how do I code this to make vaData a 1d array
with out having to use the looping code below?
vaData = Range("rngItemNo").Value
Stop

' This loop loads the array as 1d, i'm looking for alternate ways to
accomplish this. Preferably a 1 line assigment statement.
ReDim vaData(i)
Dim c As Range
k = 0
For Each c In Range("rngItemNo").Cells
k = k + 1
vaData(k) = c.Value
Next c

ArrayDone:
Stop 'and look at the Locals
End Sub

 

[Guest]

Just saw Tom's reply on another thread that answers this...

vaData = Application.Transpose(Range("rngItemNo").Value)

Thanks Tom!

 

[JE McGimpsey]

One way:

Vertical:

Dim v As Variant
Dim i As Long
v = Application.Transpose(Range("A1:A10").Value)
For i = LBound(v) To UBound(v)
Debug.Print i, v(i)
Next i

Horizontal:

Dim v As Variant
Dim i As Long
With Application
v = .Transpose(.Transpose(Range("A1:J1").Value))
End With
For i = LBound(v) To UBound(v)
Debug.Print i, v(i)
Next i

 

[Guest]

John, the answer in the other thread was directed at you since you asked the
question in that thread as well. Here it is again for any others (although I
see J.E. McGimpsey has responsed here with an excellent answer).

For a single column Range
v = Application.Transpose(Range("G1:G10").Value)

v is a 1d array.

If the range were Horizonal (single row range), then you would need

v =Application.Transpose(Application.Transpose(Range("A1:M1").Value))