Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles

Dim myArr As Variant
myArr = Application.Transpose(Range("a1:a100").Value2)

IIRC, this will break in some versions of excel if you have more than 5461
elements.

Charles wrote:

Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles

--

Dave Peterson

I am impressed. Very efficient!

thanks
Charles


Dave Peterson wrote:
Dim myArr As Variant
myArr = Application.Transpose(Range("a1:a100").Value2)

IIRC, this will break in some versions of excel if you have more than 5461
elements.

Charles wrote:

Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles

--

Dave Peterson

You can also readily "destroy" the second dimension of a 2-D
*horizontal* array. The most commonly referred to way is

myArr = Application.Transpose(Application.Transpose(Range( "a1:z1").Value2))

Another is

myArr = Application.Index(Range("a1:z1").Value2, 1, 0)

Alan Beban

Charles wrote:
I am impressed. Very efficient!

thanks
Charles


Dave Peterson wrote:
Dim myArr As Variant
myArr = Application.Transpose(Range("a1:a100").Value2)

IIRC, this will break in some versions of excel if you have more than 5461
elements.

Charles wrote:
Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles
--

Dave Peterson

Hi

Public Sub Test()
Dim MyArray() As Variant
Dim rngTest As Range
Dim RowNum As Integer
Dim ColNum As Integer


Set rngTest = [TestRange]
RowNum = rngTest.Rows.Count
ColNum = rngTest.Columns.Count
ReDim MyArray(RowNum, ColNum)
MyArray = rngTest


End Sub

TestRange is a dynamic named range, defined in workbook, something like
=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1;$1))


Arvi Laanemets



"Charles" wrote in message
oups.com...
Hello

I am looking for a more elegant (and shorter) solution to grab the
content of an excel range into an array:

Dim grab
Dim value()
grab = Range("A1:A100").Value2
ReDim value(1 To UBound(grab))
For i = 1 To UBound(grab)
value(i) = grab(i, 1)
Next i

I could just use the array grab but then I have to carry a two
dimensional syntax all the way, although I know that grab only contains
one column. Or is there a simple instruction to "destroy " one of the
dimensions of an array?

Thanks ina advance
Charles