1d Variant arrays?
 

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

--
Regards,
John

1d Variant arrays?
 

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

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

Thanks Tom!
--
Regards,
John


"John Keith" wrote:

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

--
Regards,
John

1d Variant arrays?
 

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




In article ,
John Keith wrote:

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

1d Variant arrays?
 

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

--
Regards,
Tom Ogilvy


"John Keith" wrote:

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

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

Thanks Tom!
--
Regards,
John


"John Keith" wrote:

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

--
Regards,
John