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

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

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 <50A6B94B-EB85-4B64-8115-(E-Mail Removed)>,
John Keith <(E-Mail Removed)> 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

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