1d Variant arrays?

J

John Keith

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
 
J

John Keith

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

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

Thanks Tom!
 
J

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
 
T

Tom Ogilvy

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top